Salin kolom dari beberapa lembar menjadi satu vba

@ tridi94

Sub Macro1() Dim i As Integer Dim j As Integer Dim z As Integer z = 4 j = 3 For i = 2 To Worksheets.Count Worksheets("main").Cells(j, 3).Value = Worksheets(i).Cells(1, 1).Value Worksheets("main").Cells(z, 4).Value = Worksheets(i).Cells(3, 3).Value Worksheets("main").Cells(z + 1, 4).Value = Worksheets(i).Cells(4, 3).Value Worksheets("main").Cells(z, 5).Value = Worksheets(i).Cells(3, 4).Value Worksheets("main").Cells(z + 1, 5).Value = Worksheets(i).Cells(4, 4).Value j = j + 4 z = z + 4 Next i End Sub

Mungkin dengan baris kode ini

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 Range _

Langkah 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 = True

Langkah 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 With _

Langkah 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) + 1 _

Langkah 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 If _

Langkah 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 0

Di 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 1

Berfungsi 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 2

Modul 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

1. Buka jendela Editor VBA atau Tekan Alt+F11
2. Sisipkan modul baru dari menu Sisipkan
3. Salin prosedur dan fungsi di atas dan tempel di modul yang baru dibuat
4. Anda dapat memasukkan beberapa data sampel dalam beberapa lembar. dan menjalankan prosedur

Unduh Buku Kerja Proyek – File Makro Excel