Dim wbk As Workbook
Dim shtCol As Worksheet
Dim rngComb As Range, rngDT As Range
Dim lRows As Long, lRecords As Long
Dim lMonth As Long, lYear As Long
Dim sMsg As String
Application.ScreenUpdating = False
sMsg = "Penggabungan Selesai." & vbCrLf & vbCrLf & _
"SheetName: | RowsCount:" & vbCrLf
*Set wbk = workbboks.Open("Z:\excelku\Book1.xlsm")*
Set rngComb = *wbk.*Sheets("combine").Range("a6")
rngComb.CurrentRegion.Offset(1).EntireRow.Delete
For Each shtCol In *wbk.*Sheets(Array(Sheet1.Name, Sheet2.Name,
Sheet3.Name, _
Sheet4.Name, Sheet5.Name, Sheet6.Name, _
Sheet7.Name, Sheet8.Name, Sheet9.Name, _
Sheet10.Name, Sheet11.Name,
Sheet12.Name))
Yang biru bisa diganti-ganti dengan nama file lain (gantian dari book1
sampai bookN)
kalau gak mau manual, pelajari dasar VBA yang ada dimilis tentang set nilai
cell, ambil nilai cell, loop dengan for next dan loop dengan for each next
Milis sudah menyediakan di bagian files. Berkunjunglah ke milis disini :
http://tech.groups.yahoo.com/group/belajar-excel
Disana ada folder files dan sebagainya.
Kid.
On Thu, Sep 29, 2011 at 21:07, dioni aditama <[email protected]>wrote:
> **
>
>
> Kok error ya pak,variabel not defined.logikanya kan saya manggil book1 trus
> copy 12 tabel pd 12 sheet seperti yg sudah. kok masih gak bisa ya??
>
> Public Sub GabungBook1()
> Dim wbk As Workbook
> Set wbk = workbboks.Open("drive:D\excelku\Book1.xlsm")
> Dim shtCol As Worksheet
> Dim rngComb As Range, rngDT As Range
> Dim lRows As Long, lRecords As Long
> Dim lMonth As Long, lYear As Long
> Dim sMsg As String
>
> Application.ScreenUpdating = False
> sMsg = "Penggabungan Selesai." & vbCrLf & vbCrLf & _
> "SheetName: | RowsCount:" & vbCrLf
> Set rngComb = Sheets("combine").Range("a6")
> rngComb.CurrentRegion.Offset(1).EntireRow.Delete
> For Each shtCol In Sheets(Array(Sheet1.Name, Sheet2.Name, Sheet3.Name,
> _
> Sheet4.Name, Sheet5.Name, Sheet6.Name,
> _
> Sheet7.Name, Sheet8.Name, Sheet9.Name,
> _
> Sheet10.Name, Sheet11.Name,
> Sheet12.Name))
> lMonth = lMonth + 1
> lYear = Year(Date)
>
> 'hitung jumlah baris data
> lRows = WorksheetFunction.Count(shtCol.Range("a:a").EntireColumn)
> sMsg = sMsg & shtCol.Name & vbTab & vbTab & lRows & vbCrLf
> If lRows > 0 Then
> With rngComb.Offset(1)
> 'copas
> shtCol.Range("a7").Resize(lRows, 6).Copy
> .Offset(lRecords).Resize(1, 1)
>
> 'susun data tanggal yang baik
> .Offset(, 12).Resize(1, 1).Formula = "=date(" & lYear & ","
> & lMonth & ",0)" 'template tanggal bertype datetime
> .Parent.Calculate 'kalkulasi sheet
> (antisipasi setting calculation manual)
> 'copas values add template tanggal ke data tanggal
> .Offset(, 12).Resize(1, 1).Copy
> .Offset(lRecords).Resize(lRows, 1).PasteSpecial
> xlPasteValues, xlPasteSpecialOperationAdd
> .Offset(lRecords).Resize(lRows, 1).NumberFormat =
> "DD-MMM-YYYY"
> .Offset(, 12).Resize(1, 1).ClearContents 'hapus
> template datetime
>
> lRecords = lRecords + lRows
> End With
> End If
> Next shtCol
>
> Mohon bantuannya pak
>
> Terima kasih
>
> Dioni
> --- Pada *Ming, 18/9/11, Kid Mr. <[email protected]>* menulis:
>
>
> Dari: Kid Mr. <[email protected]>
> Judul: Re: [belajar-excel] Re: Menggabungkan tabel antar book
> Kepada: [email protected]
> Tanggal: Minggu, 18 September, 2011, 10:04 PM
>
>
>
>
> Jadi data ini *tidak akan digunakan *sebagai data source penyusunan
> report.
>
> dim wbk as workbook
> set wbk=workbboks.open( "*drive:\foldernya\nama filenya.ekstensinya*")
>
> Yang di *biru *disesuaikan dengan *file fullname*.
>
> Kid.
>
>
>
>
>
>
>
