Diposting oleh DodDy
Tuesday, March 8, 2011

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.

Selamat Mencoba!

, edit post

9 komentar

  1. All Yazid Says:
    Posted on August 4, 2011 at 9:15 PM

  2. Mas, Doddy..
    Huda kembali..
    Misalkan data sumber bukan terdiri dari sheet bisa tidak?
    jadi data sumber hanya ada di satu sheet,

    mohon penjelasan

     
  3. AHASS PAKARTI MOTOR (APM) Kweni Bantul Says:
    Posted on August 8, 2011 at 6:15 AM

  4. minta tolong,sy ingin membuat laporan data2 konsumen dg sistem macro.gmna caranya yaw?saya bekerja di bengkel

     
  5. Anonymous Says:
    Posted on November 18, 2011 at 2:09 PM

  6. gan mau sedikit tanya...
    saya dapat tugas tentang excel
    contoh
    A B
    data1 = 01547787 01454878 = perkusi
    data2 = 01545487 01547787 = tenol
    data3 = 01454878 01545487 = mozzila

    nah saya ingin menjadi seperti ini
    data1 = tenol
    data2 = mozzila
    data3 = perkusi

    bisa di bantu gan...makasih ^_^

     
  7. DodDy Says:
    Posted on December 17, 2011 at 12:59 AM

  8. @ Huda: Bisa aja mas, codingnya mungkin harus dirubah sesuai kondisi filenya
    @ Ahass: caranya? ya belajar macro mas. coba baca2 refensi tentang macro. pasti akan banyak membantu Anda dalam mengolah data service atau stock.
    @ Anonymous 18/11: Bisa mas, pake rumus VLOOKUP aja. gampang kok!

     
  9. Syam_u Says:
    Posted on May 11, 2012 at 7:01 PM

  10. Mas mau nanya segalian mohon bantuannya, gimana caranya supaya data yang saya ketikkan dari userform tercetak dalam database dan bagian invoice agar dapat diprint sebagai referensi saya udah lampirkan di file contoh di http://milis-belajar-excel.1048464.n5.nabble.com/belajar-excel-invoice-dengan-macro-dan-databese-tt5688755.html

    mohon pencerahannya dan pencelasan kode agar saya mudah memahaminya

     
  11. DodDy Says:
    Posted on May 12, 2012 at 8:53 AM

  12. Syam_u: Untuk membuka form "Entry Data Invoice" secara otomatis saat file dibuka, maka pada VB Editor > ThisWorkbook di beri code:

    Private Sub Workbook_Open()
    Sheets(1).Select
    Call invoice_Click
    End Sub

    Untuk input otomatis ke dalam sheet "Invoice Cetak" dan "Database" juga bisa dilakukan dengan syntax:

    Range("Invoice cetak!F12").Value = cbo_kode.Value
    Range("Database!K6").Value = cbo_kode.Value

    Tapi jika jumlah data/baris selalu berbeda, maka harus definisi "baris" harus ditentukan terlebih dahulu. Syntaxnya menjadi sbb:

    baris = *harus didefinisikan dulu*
    Range("Invoice cetak!F" & baris).Value = cbo_kode.Value
    Range("Database!K" & baris).Value = cbo_kode.Value

    Code ini sudah saya gunakan untuk menambah/mengurangi data member di BillingFormula.xls :)

     
  13. Anonymous Says:
    Posted on November 24, 2012 at 2:38 PM

  14. Mas, tolong bantu,
    Misalkan
    Colom A B C D
    A = 019872
    B = nia
    C = angga
    D = jika C = B maka hasilnya = A
    D = jika C tidak sama dengan B maka C mencari nama yang sama dengan B dan hasilnya data yang ada di kolom A pertama sampai terakhir.

     
  15. Unknown Says:
    Posted on August 27, 2013 at 10:12 PM

  16. bagaimana jika copy data dari 1 sheet ke banyak sheet

     
  17. Anonymous Says:
    Posted on February 19, 2014 at 11:07 PM

  18. Selamat siang Mas Dodi,
    saya sudah baca "copy isi banyak sheet dalam sekejap". bagaimana caranya file sumbernya ada 5 file (anggap aja file bln1.xlsx, bln2.xlsx, bln3.xlsx, bln4.xlsx dan bln4.xlsx Dari 5 file tsb terdapat sheet2 yang sama tergantung transaksi. (misal sheet pro1,pro2,pro3,pro4,pro5).
    bagaimana macronya agar file target akan mengcopy sheet yang sama satu per satu sheet, bukan sheet digabung dalam 1 sheet. misal file targetnya tarpro1.xlsx, tarpro2.xlsx, tarpro3.xlsx, tarpro4.xlsx dan tarro5.xlsx
    untuk tarpro1.xlsx akan ada sheet pro1,pro1(2),pro1(3),pro1(4) dan pro1(5).
    mudah2an mas dodi mengerti ilustrasi saya dan bisa membuatkan macronya.
    terima kasih,
    ASL

     

Post a Comment

Recent from Blog

 
Follow me

    About Me

    My Photo
    DodDy

    Di dunia maya saya lebih dikenal sebagai penulis artikel "Tips Membuat Billing Warnet dengan Excel" yang saya tulis untuk IlmuKomputer.Com. Walau sebenarnya ilmu Excel saya masih jauh dari sempurna, tapi saya mencoba sharing ilmu Excel yang saya punya kepada Anda yang juga tertarik atau pekerjaan sehari-harinya menggunakan Excel.

    View my complete profile