formula lanjutan

Copy ISI Banyak Sheet dalam Sekejap!

Mengkopi banyak sheet memang mudah dengan menu Move or Copy sheet. Tapi bagaimana jika ingin copy isinya saja dengan jumlah sheet yang banyak? Apakah Anda akan melakukan Copy dan Paste satu per satu sheet?

Kenapa harus copy isinya saja? Kenapa tidak copy sheet saja biar mudah dan tidak repot?

Jika Anda mempunyai file dengan banyak sheet di mana terdapat sheet rekap yang berisi rumus yang link dengan sheet-sheet tersebut, tentunya jika melakukan Copy Sheet dan Delete Sheet rumus Anda akan menjadi #REF! semua!

Biar mempermudah, berikut gambarannya:

File Sumber: Adalah file yang berisi data mentah yang tidak terdapat rumus apapun. Nama dan jumlah sheet adalah baku, misal terdapat 10 cabang dengan kode cabang BD, MT, SN, CD, AY, PK, PG, GR, BU, dan TP.







File Target: Adalah file yang terdapat 10 sheet cabang dengan nama BD, MT, SN, CD, AY, PK, PG, GR, BU, TP dan sheet Rekap yang merupakan rumus yang link ke sheet-sheet tersebut.






Dengan memanfaatkan macro, maka copy isi sheet akan sangat mudah dilakukan. Berikut caranya:
Pada file target buka Visual Basic Editor (Alt + F11)
Pilih Insert > Module
Tuliskan kode macro berikut:

Sub copy_data()

Dim sumber As String
Dim target As String

sumber = Range("Rekap!J1") 'Range J1 pada sheet rekap adalah nama file target
target = Range("Rekap!J2") 'Range J2 pada sheet rekap adalah nama file sumber

On Error GoTo ErrorHandler

MsgBox "Apakah file " + sumber + " sudah dibuka?", Buttons:=vbOKOnly + vbQuestion, Title:="Copy Sheet dalam Sekejap - Powered by http://formula-excel.blogspot.com"

Windows(sumber).Activate 'Mengaktifkan file sumber
Sheets("BD").Activate 'Memilih sheet BD file sumber
Columns("A:P").Select 'Menyeleksi kolom A s/d P (kolom yang akan dicopy)
Selection.Copy 'Copy
Windows(target).Activate 'Mengaktifkan file target
Sheets("BD").Activate 'Memilih sheet BD file target
Columns("A:P").Select 'Menyeleksi kolom A s/d P (kolom yang akan dipaste)
ActiveSheet.Paste 'Paste
Application.CutCopyMode = False 'Menonaktifkan seleksi
Range("C1").Select 'Meletakkan kursor di C1

Windows(sumber).Activate
Sheets("MT").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("MT").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("SN").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("SN").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("CD").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("CD").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("AY").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("AY").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("PK").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("PK").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("PG").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("PG").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("GR").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("GR").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("BU").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("BU").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

Windows(sumber).Activate
Sheets("TP").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("TP").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select

GoTo WrapUp

ErrorHandler:

MsgBox "Coba periksa lagi:" & vbCrLf & vbCrLf & "1. Apakah file sumber bernama " + sumber + "? " & vbCrLf & "2. Apakah file " + sumber + " sudah dibuka? " & vbCrLf & "3. Apakah file ini bernama " + target + "?" & vbCrLf & "4. Apakah jumlah dan nama sheet dari file target sama dengan jumlah dan nama sheet di file sumber? " & vbCrLf & vbCrLf & "Jika sudah benar, silahkan jalankan kembali.", vbOKOnly + vbCritical, "Copy Sheet dalam Sekejap - Powered by http://formula-excel.blogspot.com"
Err.Clear

WrapUp:

Application.ScreenUpdating = True
Application.Interactive = True

End Sub

Sub clear_format()
Columns("A:P").Select
Selection.UnMerge
Selection.ClearContents
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = True
Selection.Font.Bold = False
Range("C1").Select
End Sub

Sub delete_data()

Sheets("BD").Activate 'Memilih sheet BD
clear_format 'Menjalankan macro clear_format

Sheets("MT").Activate
clear_format

Sheets("SN").Activate
clear_format

Sheets("CD").Activate
clear_format

Sheets("AY").Activate
clear_format

Sheets("PK").Activate
clear_format

Sheets("PG").Activate
clear_format

Sheets("GR").Activate
clear_format

Sheets("BU").Activate
clear_format

Sheets("TP").Activate
clear_format

End Sub

Terdapat 3 kode macro yaitu:

copy_data() yang fungsinya untuk mengkopi sheet BD, MT, SN, CD, AY, PK, PG, GR, BU, TP dari file sumber ke file target
delete_data() yang fungsinya untuk memilih sheet pada file target dan menjalankan macro clear_format.
clear_format() yang fungsinya untuk menghapus isi (clear content - bukan hapus baris/kolom).
Kembali pada Excel:

Pada sheet Rekap sel J1 tuliskan nama file sumber. Misal File-Sumber.xls
Pada sheet Rekap sel J2 tuliskan nama file target. Misal File-Target.xls

Selanjutnya Anda dapat menjalankan macro copy_data() atau delete_data() melalui menu Tools > Macro > Macros atau Alt + F8.