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.
