ExcelMacro - gabung data sheet cabang (2) contoh file dan penjelasan singkat code macro

melanjutkan postingan sebelumnya excelmacro-gabung-data-sheet-cabang, kali ini ane akan menjelaskan
singkat alur proses code macro, berikut :


1) memberikan nama modul macro dengan sebutan gabung()

      Sub gabung()

      ...

      End Sub

2) mendeklarasikan variabel type long, yang nantinya digunakan untuk menampung baris akhir data di sheet 
    Gabung dan Cabang (penjelasan type data lihat dimari gan >> vb-visual-basic-tipe-data-variabel)

    Dim brsAkh, akhBrs As Long


3) mengisikan jam mulai proses macro pada range J4

    Range("J4").Value = Format(Now, "hh:mm:ss")

  
4) mengisikan baris terakhir (baris paling bawah yang terisi) sheet Gabung ke variabel brsAkh

    brsAkh = Range("B" & Rows.Count).End(xlUp).Row + 1


5) jika variabel brsAkh nilainya lebih besar dari 4, maka range A5 s/d E baris ke brsAkh (baris terakhir
    sheet Gabung) dikosongkan isinya. Ingat pada artikel sebelumnya gabung-data-sheet-cabang bahwa 
    tampilan pada sheet Gabung, header data terletak pada baris 4, artinya jika baris terakhir yang telah
    dimasukan ke variabel brsAkh nilainya lebih dari 4, berarti ada data bekas proses macro sebelumnya,
    sehingga data ini harus dihapus agar tidak terjadi dobel data jika macro diproses berulang-ulang.
    Detail penjelasan fungsi IF / perumpamaan / percabangan ada di mari >> if-macro.
     
      If brsAkh > 4 Then
      Range("A5:E" & brsAkh).Value = Empty
      End If

6) Jika sebelumnya menjelaskan fungsi IF, dalam langkah selanjutnya akan dipraktekan fungsi perulangan
    untuk penjelasan dasarnya bisa dicek >> perulangan FOR. Setelah paham dasar-dasar fungsi perulangan
    FOR, kode macro selanjutnya akan saya terjemahkan dalam bahasa Indonesia, mungkin kalimatnya agak
    aneh, tapi jika agan paham dasar-dasar fungsi perulangan pasti agan akan paham penjelasan dibawah,
    (tulisan warna abu2)
     
      'Untuk variabel i yang berisi nilai mulai dari 1 s/d jumlah sheet, nomer sheet dimulai dari kiri kanan, 
      For i = 1 To Sheets.Count
          'Jika 6 dari kiri nama sheet ke i huruf kecilnya adalah "cabang" (tanpa petik) maka
          '** LCase berfungsi merubah data type string/teks menjadi  huruf kecil semua, pada macro ini fungsi
                untuk memastikan bahwa apapun format kapital huruf nama sheet ke i, selama kata pertamanya 
                adalah "cabang" maka dianggap memenuhi kondisi IF
          If Left(LCase(Sheets(i).Name), 6) = "cabang" Then          'fungsi IF ke 1 

          'Mengisikan variabel akhBrs dengan nilai baris terakhir yang ada isinya pada kolom B di sheet ke i
          akhBrs = Sheets(i).Range("B" & Rows.Count).End(xlUp).Row

              'Jika variabel akhBrs nilai lebih dari 1 maka

              '** akhBrs bernilai 1 artinya tidak ada data pada sheet ke i (sheet yang namanya berawalan 

                    dengan kata "cabang" (header ada dibaris 1)
              If akhBrs > 1 Then            'fungsi IF ke 2 

              'Lakukan copy sheet ke i kolom B baris ke 2 s/d kolom D baris ke brsAkh (isinya variabel 

              BrsAkh)

              Sheets(i).Range("B2:D" & akhBrs).Copy _
 
              'Pastekan copy data ke sheet Gabung kolom B baris setelah baris terakhir yang ada isinya
              Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1)

              'Mengisikan nama sheet Cabang ke kolom E baris data yang baru ditambahkan
              Range("E" & Range("E" & Rows.Count).End(xlUp).Row + 1 & ":E" & _

              Range("B" &  Rows.Count).End(xlUp).Row).Value = Sheets(i).Name

              End If                               'penutup fungsi IF ke 2
          End If                                   'penutup fungsi IF ke 1
      Next i                                      

Sementara ini dulu penjelasannya,
Contoh file macro Gabung data cabang bisa didonlot >> GabungDataCabang.zip

Read More..

ExcelMacro - gabung data sheet cabang

Berikut adalah contoh macro menggabungkan data pada sheet Cabang ?? ke sheet Gabung. Misal
kita buat file excel dengan nama GabungDataCabang.xls, di dalamnya terdapat 4 sheet yaitu : 

-  Gabung berfungsi sebagai tempat tampungan semua data yang ada di semua sheet Cabang ?? 
-  Cabang berisi data transaksi penjualan, format namanya Cabang<spasi>angka misal : Cabang 1,
   Cabang 2, Cabang 3 dst. Untuk contoh ada 3 sheet Cabang.

Jadi total sheet ada 4, yaitu Gabung, Cabang 1, Cabang 2 dan Cabang 3. Untuk sheet Gabung, atur
tampilannya menjadi sperti berikut :

Kotak dengan tulisan "Gabung Report" adalah shape yang saya modif, yang berfungsi sebagai tombol
untuk menjalankan macro. Sedangkan untuk sheet Cabang atur format tampilan menjadi seperti berikut :

Yang perlu diperhatikan adalah posisi header tabel, dimana pada sheet Cabang ini, header tabel data

berada di baris 1 dan diskripsi header mulai dari kolom A s/d D adalah No, Diskripsi, Jumlah dan tanggal.
Jumlah baris dan isi data di setiap sheet Cabang bisa berbeda-beda, diumpakan sebagai transaksi sales
masing-masing Cabang (jumlah sheet Cabang bisa ditambah / dikurangi dengan format penamaan seperti
di atas). 
Kemudian buka jendela Visual Basic Editor (jika belum tahu silahkan dimari >> memulai-makro-excel ).

Setelah jendela Visual Basic Editor terbuka ketikan code berikut :

  Sub gabung()
  Dim brsAkh, akhBrs As Long
  Range("J4").Value = Format(Now, "hh:mm:ss")
  brsAkh = Range("B" & Rows.Count).End(xlUp).Row + 1
      If brsAkh > 4 Then
      Range("A5:E" & brsAkh).Value = Empty
      End If
      For i = 1 To Sheets.Count
          If Left(LCase(Sheets(i).Name), 6) = "cabang" Then
          akhBrs = Sheets(i).Range("B" & Rows.Count).End(xlUp).Row
              If akhBrs > 1 Then
              Sheets(i).Range("B2:D" & akhBrs).Copy _
              Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
              Range("E" & Range("E" & Rows.Count).End(xlUp).Row + 1 & ":E" & _

              Range("B" &  Rows.Count).End(xlUp).Row).Value = Sheets(i).Name
              End If
          End If
      Next i
  Range("A5:A" & Range("B" & Rows.Count).End(xlUp).Row).Formula = "=Row()-4"
  Range("J7").Formula = "=SUM(C5:C" & Range("B" & Rows.Count).End(xlUp).Row & ")"
  Range("J5").Value = Format(Now, "hh:mm:ss")
  Range("J6").Value = Format(Range("J5") - Range("J4"), "hh:mm:ss")
  MsgBox "Proses selesai !" & Chr(13) & "Lama " & Format(Range("J6"), "hh:mm:ss"), _

      vbInformation, "Info"
  End Sub

Setelah code macro di atas dicopykan/diketik, pilih sheet Gabung kemudian klik kanan gambar "Gabung
Report" pilih Assign Macro . . .  , maka akan muncul jendela Assign Macro, kemudian klik gabung dan tekan OK.

Selanjutnya macro gabung data cabang sudah bisa di jalankan dengan cara klik gambar "Gabung Report"
(tombol proses macro). Silahkan cek perubahan yang terjadi. :)

Selesai!! sementara ini dulu, untuk penjelasan macro dan contoh file menyusul  ;P

Semoga bermanfaat

Read More..