Bagaimana cara menggabungkan data dari beberapa lembar kerja di excel vba?

Misalnya Anda memiliki 100 lembar data dan semua lembar kerja memiliki struktur yang sama. Yang kami inginkan adalah mendapatkan semua data beberapa lembar kerja dikonsolidasikan ke dalam satu lembar kerja. Lakukan dalam hitungan detik dengan kode berikut

Catatan. Kode ini mengasumsikan bahwa SEMUA lembar kerja memiliki struktur bidang yang sama; . Kode menyalin semua baris menjadi satu lembar kerja baru yang disebut Master.  

Sub CopyFromWorksheets() 
    Dim wrk As Workbook 'Workbook object - Always good to work with object variables
    Dim sht As Worksheet 'Object for handling worksheets in loop
    Dim trg As Worksheet 'Master Worksheet
    Dim rng As Range 'Range object
    Dim colCount As Integer 'Column count in tables in the worksheets
     
    Set wrk = ActiveWorkbook 'Working in active workbook
     
    For Each sht In wrk.Worksheets 
        If sht.Name = "Master" Then 
            MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _ 
            "Please remove or rename this worksheet since 'Master' would be" & _ 
            "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error" 
            Exit Sub 
        End If 
    Next sht 
     
     'We don't want screen updating
    Application.ScreenUpdating = False 
     
     'Add new worksheet as the last worksheet
    Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 
     'Rename the new worksheet
    trg.Name = "Master" 
     'Get column headers from the first worksheet
     'Column count first
    Set sht = wrk.Worksheets(1) 
    colCount = sht.Cells(1, 255).End(xlToLeft).Column 
     'Now retrieve headers, no copy&paste needed
    With trg.Cells(1, 1).Resize(1, colCount) 
        .Value = sht.Cells(1, 1).Resize(1, colCount).Value 
         'Set font as bold
        .Font.Bold = True 
    End With 
     
     'We can start loop
    For Each sht In wrk.Worksheets 
         'If worksheet in loop is the last one, stop execution (it is Master worksheet)
        If sht.Index = wrk.Worksheets.Count Then 
            Exit For 
        End If 
         'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
        Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 
         'Put data into the Master worksheet
        trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value 
    Next sht 
     'Fit the columns in Master worksheet
    trg.Columns.AutoFit 
     
     'Screen updating should be activated
    Application.ScreenUpdating = True 
End Sub

Cara Penggunaan

• Buka buku kerja yang berisi lembar kerja yang ingin Anda gabungkan
• Tekan Alt+F11 untuk membuka Visual Basic Editor (VBE)
• Dari menu, pilih Insert-Module
• Salin & Tempel kode di atas ke jendela kode di sebelah kanan
• Simpan file dan tutup VBE

Unduh File Sampel. Konsolidasikan Beberapa Lembar Kerja. xls

Baca juga

• Cara Membuka Proteksi Lembar Excel Tanpa Kata Sandi
• Menggabungkan beberapa file excel menjadi satu spreadsheet – VBA
• PivotTable Tingkat Lanjut. Menggabungkan Data dari Beberapa Lembar
• Cara Menggabungkan Teks dari Banyak Sel
• Lebih Banyak Alat & Tip VBA

Bagikan ini

• Facebook
• Twitter
• LinkedIn
• Surel
• Telegram

Terkait

Salah satu masalah umum dalam mengelola data adalah menyatukan semuanya. Katakanlah kita memiliki beberapa data yang tersebar di beberapa lembar yang ingin kita satukan dalam satu lembar. Bagaimana Anda akan melakukannya?

Salah satu caranya adalah menyalinnya dari beberapa lembar dan menempelkannya di satu lokasi atau yang lebih cerdas adalah dengan menulis makro sederhana untuk melakukan hal yang sama untuk kita.

Asumsikan kumpulan data ini

Kami telah menyebarkan data pada 5 lembar berbeda. Perhatikan juga bahwa tajuk dalam data adalah sama (itu adalah situasi yang lebih disukai)

Berikut adalah makro yang akan menggabungkan data

Sub combinedata()
Dim var As Integer
Dim sh As Worksheet
var = 0
For Each sh In Worksheets
 If sh.Name = 'Consolidated Data' Then
 var = 1
 Exit For
 End If
Next sh
If var = 0 Then Sheets.Add(Before:=Sheets(1)).Name = 'Consolidated Data' Else
Sheets('Consolidated Data').Move Before:=Sheets(1)
Sheets(2).Activate
Sheets(2).Range(Range('a1'), Range('A1').End(xlToRight)).Copy
Sheets(1).Activate
Sheets('Consolidated Data').Paste Destination:=Range('a1')
For Each sh In Worksheets
 If sh.Name <> ActiveSheet.Name Then
 With sh
 .Range('A2:N' & .Range('A' & Rows.Count).End(xlUp).Row).Copy _
 Range('A' & Range('A' & Rows.Count).End(xlUp).Row + 1)
 End With
 End If
Next sh
ActiveWindow.DisplayGridlines = False
Range('A1').CurrentRegion.Select
Selection.Columns.AutoFit
End Sub

>> Unduh buku kerja contoh dari bawah <<

⬇️ Masukkan Nama & Email Anda untuk mendapatkan file

berlangganan Newsletter

Ikuti Langkah-langkahnya

1. Salin Kode ini
2. Buka buku kerja excel tempat Anda ingin menggabungkan data
3. Tekan pintasan Alt + F11 untuk membuka Jendela Visual Basic
4. Di Menu Sisipkan, klik Modul atau gunakan pintasan Alt i m untuk menambahkan Modul. Modul adalah tempat di mana kode ditulis
5. Di modul kosong tempel kode dan tutup Editor Visual Basic
6. Kemudian gunakan shortcut Alt + F8 untuk membuka Macro Box. Anda akan memiliki daftar semua makro di sini
7. Anda akan melihat Makro yang baru saja Anda tempel di Modul sebagai 'CombineData'
8. Menjalankannya. Lembar baru (data konsolidasi) akan segera dimasukkan dan Makro akan secara otomatis menyalin data dari sisa lembar

Beberapa hal yang perlu diperhatikan

1. Pastikan untuk mengaktifkan makro sebelum Anda mulai bekerja dengan kode
2. Kode ini bersifat dinamis dan juga dapat digunakan di buku kerja Anda sendiri
3. Kode akan membuat sheet baru sebagai Consolidated Data untuk menggabungkan data. Jika Anda sudah memiliki sheet tersebut di buku kerja, data akan digabungkan dalam sheet tersebut
4. Bagian bertanda biru dan tebal dalam kode dapat diubah agar sesuai dengan rentang data Anda

Beberapa Makro berguna lainnya untuk membuat hidup Anda lebih mudah

1. Filter Otomatis dengan Makro
2. Menampilkan Beberapa Lembar Sekaligus
3. Ubah Angka menjadi Kata Mata Uang India
4. Membuat Indeks dari semua nama Sheet

Apakah Anda sering menggabungkan data dari beberapa lembar?

Bagaimana cara menyalin data dari beberapa lembar kerja menjadi satu menggunakan VBA?

Bagaimana cara menggabungkan data di Excel VBA?

Apa langkah-langkah untuk menggabungkan data dari beberapa lembar kerja?

Bagaimana cara menarik data dari beberapa lembar kerja di Excel menggunakan makro?