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!

Read More

Menjinakkan Hasil Rumus Yang Error

Hasil rumus yang error seperti #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL! selain akan membuat tampilan tabel Excel Anda tidak enak dipandang juga tidak akan bisa dijumlahkan. Tapi hanya dengan rumus sederhana IFERROR maka hasilnya akan menjadi lebih baik.

Syntaxnya adalah =IFERROR(value, value_if_error)

• Value = nilai atau sel yang error
• Value_If_Error = nilai yang akan dimunculkan jika sel tersebut error. Bisa diisi dengan kosong "", nol (0), atau teks apa saja, misal "Asset Tidak Ada"

Contohnya sbb:

Baris 3 s/d 9 menggunakan rumus VLOOKUP biasa tanpa ditambahkan rumus IFERROR. Jika Nama Asset yang dicari tidak ada, maka hasilnya adalah #N/A. Begitu juga kolom Nilai. Jika dijumlahkan (baris 10) maka hasilnya akan #N/A juga.

Sedang baris 14 s/d 20 ditambahkan rumus IFERROR sehingga hasil #N/A akan diganti menjadi kalimat "Asset Tidak Ada" dan angka nol "0" pada kolom Nilai. Sehingga tetap bisa dijumlahkan (baris 21).

Penjelasan rumus:

• F14: =IFERROR(VLOOKUP(E14,$A$2:$C$83,2,0),"Asset Tidak Ada")
• Jika hasil dari (VLOOKUP(E14,$A$2:$C$83,2,0) adalah #N/A maka yang muncul adalah "Asset Tidak Ada".
• Namun jika hasilnya tidak #N/A, maka keterangan yang muncul adalah hasil dari VLOOKUP. 
• G14: =IFERROR(VLOOKUP(E14,$A$2:$C$83,3,0),0)
• Jika hasil dari (VLOOKUP(E14,$A$2:$C$83,3,0) adalah #N/A maka yang muncul adalah angka nol (0).
• Namun jika hasilnya tidak #N/A, maka keterangan yang muncul adalah hasil dari VLOOKUP.

Rumus IFERROR ini juga bisa digunakan untuk hasil rumus yang error seperti #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL!

Read More

Mengenal Lebih Dekat Fungsi TEXT

Apakah Anda pernah menggunakan fungsi TEXT? Fungsi TEXT adalah fungsi yang disediakan oleh Excel untuk merubah format angka, tanggal, persentase, waktu, dll menjadi format yang mudah dibaca ketika digabungkan dengan teks yang lain.

Syntaxnya mudah saja:   =TEXT(value, format_text)

• Value = nilai yang akan diformat, bisa dirujuk ke sebuah sel.
• Format_Text = Format yang akan digunakan.

Contohnya sbb:

Berikut penjelasannya:

• A1: 3/8/2011 dengan format sel "d-mmm-yy"
• B1: ="Sales "&TEXT(A1,"mmmm yyyy")
• Menggabungkan kata "Sales " dengan tanggal di A1 yang formatnya adalah "mmmm yyyy" sehingga menghasilkan kalimat "Sales March 2011"
• Jika tanpa menggunakan rumus TEXT maka hasilnya akan menjadi "Sales 40610"
• B3: ="Purchase "&TEXT(A1,"mmm-yy")
• Menggunakan format yang lain yaitu "mmm-yy" sehingga menghasilkan kalimat "Purchase Mar-11"
• A6: 10000 dengan format sel Accounting - Decimal Places = 2
• B6: ="Total Pembayaran "&TEXT(A6,"Rp 0.00")
• Menggabungkan kalimat "Total Pembayaran " dengan angka di A6 yang formatnya adalah "Rp 0.00" sehingga menghasilkan kalimat "Total Pembayaran Rp 10000.00"
• Jika tanpa menggunakan rumus TEXT maka hasilnya akan menjadi "Total Pembayaran 10000"
• B8: ="Total Penerimaan "&TEXT(A6,"Rp 0,000")
• Menggunakan format yang lain yaitu "Rp 0,000" (dengan pemisah ribuan dan tanpa dua digit desimal) sehingga menghasilkan kalimat "Total Penerimaan Rp 10,000"
• B10: ="Sebanyak "&TEXT(A6,"0,000")&" orang menghadiri acara tersebut."
• Menggunakan format yang lain yaitu "0,000" (tanpa tanda Rp dan dengan pemisah ribuan) sehingga menghasilkan kalimat "Sebanyak 10,000 orang menghadiri acara tersebut."
• A12: 85.25%
• B12: ="Penerimaan bulan ini hanya mencapai "&TEXT(A12,"0.00%")&" dibanding bulan lalu."
• Menggabungkan kalimat "Penerimaan bulan ini hanya mencapai " dengan persentase di A12 dan "dibanding bulan lalu." sehingga menghasilkan kalimat "Penerimaan bulan ini hanya mencapai 85.25% dibanding bulan lalu."
• Jika tanpa rumus TEXT maka hasilnya akan menjadi "Penerimaan bulan ini hanya mencapai 0.8525 dibanding bulan lalu."
• B14: ="Pada tanggal "&TEXT(A1,"d-mmm-yy")&" diterima pembayaran sebesar "&TEXT(A6,"Rp 0,000.00")&" atau sebesar "&TEXT(A12,"0,00%")&" dari total yang seharusnya dibayar."
• Menggabungkan antara tanggal, nilai, dan persentase menjadi suatu kalimat lengkap dengan format yang mudah dibaca "Pada tanggal 8-Mar-11 diterima pembayaran sebesar Rp 10,000.00 atau sebesar 085% dari total yang seharusnya dibayar."
• Jika tanpa rumus TEXT maka hasilnya akan menjadi "Pada tanggal 40610 diterima pembayaran sebesar 10000 atau sebesar 0.8525 dari total yang seharusnya dibayar."

Silahkan selanjutnya Anda coba-coba dengan format yang lain. Untuk memudahkan Anda dalam menentukan format yang diinginkan, contek saja dari Custom Format Cells seperti berikut:

Mau lebih maksimal bereksplorasi dengan fungsi TEXT? Silahkan belajar langsung ke sumbernya! http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx

Selamat mencoba!

Read More