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:
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
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
Sheets("BD").Activate 'Memilih sheet BD
clear_format 'Menjalankan macro clear_format
Terdapat 3 kode macro yaitu:
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
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"
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()
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
- 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.
Selamat Mencoba!
0 komentar:
Posting Komentar