@berchi ...
Permasalahan pada scriptnya adalah sebagai berikut. Untuk baris script:
With Sheet1
.Activate
'+-- Masalah #1
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If lRow < 3 Then Exit Sub
'+-- Masalah #2
vSrcData = .Range("A3:G" & lRow).Value2
End With
kita tinjau dulu bentuk tabel master datanya. Tabel master data yang Anda gunakan seperti berikut:
Tabel di atas berkaitan dengan baris script:
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
karena pada dasarnya script mengacu kepada kolom A. Perlu dipahami, baris tersebut bertujuan untuk mendapatkan baris data terakhir yang ada pada tabel tersebut. Asumsi saya pada awalnya, baris pada kolom A seharusnya akan sinkron dengan data pada kolom B. Artinya, jika kolom data pada kolom B hanya ada 2 baris, maka kolom A pun akan berisi nilai sesuai indeks datanya (1 dan 2 saja). Meskipun kesalahan ini tidak fatal (dalam pengambilan data aktualnya), namun kalau ini tidak Anda pahami, besar kemungkinan Anda akan melakukan kesalahan yang sama pada saat menyusun script serupa.
Baris tersebut dapat diperbaiki menjadi:
lRow = .Cells(.Rows.Count, "B").End(xlUp).Row
jika acuannya adalah data pada kolom B saja. Sedangkan baris script berikut:
vSrcData = .Range("A3:G" & lRow).Value2
kurang tepat karena seperti terlihat pada gambar di atas, data dimulai dari baris keempat, bukan mulai dari baris ketiga. Sehingga, seharusnya diubah menjadi:
vSrcData = .Range("A4:G" & lRow).Value2
Selanjutnya, pada baris script berikut:
'+-- 2. Periksa apakah sheet target sudah ada.
For Each xlWS In ThisWorkbook.Worksheets
For lX = 0 To UBound(vSheets)
If vSheets(lX) = xlWS.Name Then
vSheets(lX) = "_" & vSheets(lX)
'+-- Masalah #3
With xlWS.Range("A11").CurrentRegion
'+-- Masalah #4
If .Columns.Count = 15 Then
...
End If
End With
...
End If
Next
Next
Pada baris script berikut:
With xlWS.Range("A11").CurrentRegion
seharusnya sel acuannya dimulai dari sel A8, karena baris ke-8 merupakan Header dari tabel target yang akan dibuat. Dari baris tersebut akan diketahui jumlah kolom aktual dari tabel target tersebut. Sebelumnya, jumlah kolomnya adalah 15. Namun pada file terakhir yang Anda upload, sudah berubah menjadi 20. Jika sel A11 yang dijadikan acuan, maka akan terjadi kesalahan. Contohnya untuk sheet BBNI (dengan 1 baris data yang sudah ada), kalau script tersebut dieksekusi pada properti .Address
dan properti .Columns.Count
-nya, maka hasilnya adalah:
Debug.Print xlWS.Range("A11").CurrentRegion.Address '= $A$10:$E$11
Debug.Print xlWS.Range("A11").Columns.Count '= 5
Debug.Print xlWS.Range("A8").CurrentRegion.Address '= $A$8:$T$10
Debug.Print xlWS.Range("A8").Columns.Count '= 20
Terlihat perbedaan alamat dan jumlah kolom aktual dari kedua sel acuan tersebut. Hasil dari script di atas nantinya digunakan pada baris selanjutnya, yakni:
If .Columns.Count = 15 Then
Karena bentuk tabelnya sudah berubah (jumlah kolomnya bertambah) seharusnya nilai 15 pada script tersebut pun diubah menjadi 20 (sebagaimana jumlah kolom pada tabel target terakhir yang sudah Anda ubah). Tujuan dari baris script tersebut untuk mengetahui apakah data sudah pernah diinput pada tabel terkait atau belum. Jika sudah pernah diinput, maka proses akan keluar dari pengulangan. Sebaliknya jika belum, maka proses akan menyalin ulang tabel pada sheet TEMPLATE dengan tujuan bentuk dan susunannya sesuai dengan yang sudah ditentukan dan formula yang sudah ada dapat disalin ke baris berikutnya.
Terakhir, perbaiki juga baris script berikut:
'+-- 4. Salin data ke setiap sheet.
...
If lIdx > 1 Then
'+-- Masalah #5
ActiveSheet.Cells(lRow - 1, "A").Resize(2, 15).FillDown
...
End If
...
menjadi:
ActiveSheet.Cells(lRow - 1, "A").Resize(2, 20).FillDown
Demikian penjelasannya.