VBA untuk menggabungkan data di beberapa Lembar Kerja ke Lembar Kerja yang baru dibuat di buku kerja yang sama. Kisaran di semua lembar kerja digabungkan ke dalam Lembar Kerja terkonsolidasi (Lembar Kerja akhir) satu demi satu secara berurutan. Jika data tidak tersedia di Lembar Kerja Sumber (i. e Lembar Kerja Input), data tidak akan diperbarui dalam Lembar Kerja gabungan. Berikut adalah penjelasan rinci langkah demi langkah untuk mengotomatisasi proses ini menggunakan VBA
Bagaimana kita akan mengembangkan modul proyek ini (Langkah-langkah KUNCI)
Untuk mengkonsolidasikan semua lembar kerja di buku kerja, pertama-tama kita harus membuat lembar kerja baru (mari kita panggil lembar master) dan kemudian mengulang setiap lembar kerja di buku kerja. Kami harus menemukan rentang data yang valid di setiap lembar kerja dan menambahkan lembar master yang baru dibuat di akhir baris
Izinkan saya menjelaskan langkah-langkah kunci untuk mengembangkan proyek ini. Kami akan menulis prosedur (Consolidate_Data_From_Different_Sheets_Into_Single_Sheet) dengan pendekatan di bawah ini
- Langkah 1. Deklarasi. Kami akan mendeklarasikan variabel dan objek yang diperlukan yang digunakan dalam prosedur kami
- Langkah 2. Nonaktifkan Pembaruan layar dan Acara. sementara untuk menghindari kedipan layar dan pemicu peristiwa
- Langkah 3. Hapus lembar Master lama. Sebelum membuat master sheet baru, kita harus memeriksa apakah ada sheet dengan nama yang sama dan menghapusnya
- Langkah 4. Menambahkan lembar kerja baru. Mari tambahkan lembar Master baru untuk menempelkan data dari lembar lain
- Langkah 5. Ulangi setiap lembar. Sekarang, mari kita ulangi setiap lembar kerja (sebut saja lembar sumber) dan tempel di lembar master
- Langkah 5. 1. Temukan Baris Terakhir yang Tersedia. Sekarang kita harus menemukan baris terakhir yang tersedia di lembar master untuk menempelkan data
- Langkah 5. 2. Temukan Baris yang terakhir digunakan dan kolom yang terakhir digunakan. Sekarang kita harus menemukan baris dan kolom terakhir dari lembar sumber
- Langkah 5. 3. Periksa apakah ada cukup data. Informasi yang didapat dari langkah di atas akan membantu untuk memeriksa apakah data tersedia di lembar sumber
- Langkah 5. 4. Salin data jika ada. Sekarang, salin data dari lembar sumber dan tambahkan ke lembar master
- Langkah 6. Aktifkan Pembaruan layar dan Acara. Mari setel ulang pembaruan layar dan acara
Catatan. Kami akan membuat dua fungsi yang ditentukan pengguna yang akan kami gunakan pada langkah 5 untuk menemukan baris terakhir dan kolom terakhir
Sekarang, mari kita lihat kode untuk setiap langkah
Langkah 1. Mendeklarasikan variabel yang digunakan di seluruh proyek
Dim Sht As Worksheet, DstSht As Worksheet Dim LstRow As Long, LstCol As Long, DstRow As Long Dim i As Integer, EnRange As String Dim SrcRng As RangeLangkah 2. Nonaktifkan Pembaruan Layar digunakan untuk menghentikan kedipan layar dan Nonaktifkan Acara digunakan untuk menghindari kotak dialog/popup yang terputus
With Application .ScreenUpdating = False .EnableEvents = False End With _Langkah 3. Menghapus Lembar Kerja 'Consolidate_Data' jika ada di Buku Kerja. Dan Peringatan Tampilan digunakan untuk menghentikan sembulan saat menghapus Lembar Kerja
Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Sheets("Consolidate_Data").Delete Application.DisplayAlerts = TrueLangkah 4. Menambahkan Lembar Kerja baru di akhir Lembar Kerja. Penamaan sebagai 'Consolidate_Data'. Dan akhirnya ditugaskan ke objek (DstSht)
With ActiveWorkbook Set DstSht = .Sheets.Add(After:=.Sheets(.Sheets.Count)) DstSht.Name = "Consolidate_Data" End WithLangkah 5. Itu adalah lLooping melalui setiap (atau semua) Lembar Kerja di buku kerja
Dan jika pernyataan memeriksa lembar Input (Input Data) dan lembar tujuan (Lembar Konsolidasi) sama atau tidak. Jika sama maka akan memeriksa lembar kerja berikutnya. Jika tidak sama dengan maka itu menyalin data input dan menggabungkannya ke Lembar Kerja Konsolidasi
For Each Sht In ActiveWorkbook.Worksheets If Sht.Name <> DstSht.Name Then End if _Langkah 5. 1. Menemukan baris terakhir di Lembar Kerja 'Consolidate_Data' menggunakan fungsi 'fn_LastRow'
DstRow = fn_LastRow(DstSht) + 1Langkah 5. 2. Menemukan baris yang terakhir digunakan dan kolom yang terakhir digunakan di Lembar Kerja Input dan menugaskannya ke objek LstRow dan LstRow
Menemukan alamat sel yang terakhir digunakan di Lembar Kerja dan menugaskannya ke objek EnRange. Akhirnya menemukan rentang data Input di Lembar Kerja Input dan menugaskannya ke objek 'SrcRng'
LstRow = fn_LastRow(Sht) LstRow = fn_LastColumn(Sht) EnRange = Sht.Cells(LstRow, LstCol).Address Set SrcRng = Sht.Range("A1:" & EnRange) _Langkah 5. 3. Periksa apakah ada cukup baris di Lembar Kerja 'Consolidate_Data'. Kalau tidak, itu menampilkan pesan ke pengguna dan pergi ke IfError
If DstRow + SrcRng.Rows.Count > DstSht.Rows.Count Then MsgBox "There are not enough rows to place the data in the Consolidate_Data worksheet." GoTo IfError End IfLangkah 5. 4. Menyalin data dari Lembar Kerja masukan dan menggabungkannya dengan Lembar Kerja tujuan
SrcRng.Copy Destination:=DstSht.Range("A" & DstRow) _Langkah 6. Mengaktifkan Pembaruan Layar dan Acara di akhir proyek
With Application .ScreenUpdating = True .EnableEvents = True End With _Kode Modul VBA Akhir (Makro)
Temukan makro berikut untuk Mengkonsolidasikan data dari Lembar Kerja yang berbeda dari Buku Kerja
With Application .ScreenUpdating = False .EnableEvents = False End With _0Di bawah ini adalah dua fungsi yang ditentukan pengguna yang telah kami buat untuk menemukan baris terakhir dan kolom terakhir dari lembar kerja yang diberikan. Kami telah memanggil fungsi-fungsi ini dalam prosedur di atas pada langkah 5. 1 dan 5. 2
Berfungsi untuk Menemukan Baris Terakhir
Fungsi berikut akan menemukan baris terakhir dari lembar kerja yang diberikan. 'fn_LastRow' akan menerima lembar kerja (Sht) sebagai input dan memberikan baris terakhir sebagai output
With Application .ScreenUpdating = False .EnableEvents = False End With _1Berfungsi untuk Menemukan Kolom Terakhir
Fungsi berikut akan menemukan kolom terakhir dari lembar kerja yang diberikan. 'fn_LastColumn' akan menerima lembar kerja (Sht) sebagai input dan memberikan kolom terakhir sebagai output
With Application .ScreenUpdating = False .EnableEvents = False End With _2Modul Akhir
Ayo
Instruksi untuk Mengeksekusi Prosedur
Anda dapat mengunduh file di bawah ini dan melihat kode dan menjalankannya. Atau yang lain, Anda membuat buku kerja baru dan menggunakan kode di atas dan mengujinya. Berikut adalah petunjuk untuk menggunakan kode di atas
- Buka jendela Editor VBA atau Tekan Alt+F11
- Sisipkan modul baru dari menu Sisipkan
- Salin prosedur dan fungsi di atas dan tempel di modul yang baru dibuat
- Anda dapat memasukkan beberapa data sampel dalam beberapa lembar. dan menjalankan prosedur