Video ini mendemonstrasikan cara menggabungkan semua buku kerja Excel dalam folder menjadi buku kerja baru menggunakan VBA. Semua lembar di setiap buku kerja akan disalin ke buku kerja baru
Klik di sini untuk Mendaftar Kursus Ultimate Excel VBA
30+ jam - termasuk sertifikasi
Semua pembelian membantu mendukung blog ini - terima kasih
Kode VBA ditampilkan dalam video ...
Sub CombineWorkbooks() Dim Path As String Path = "C:\Users\Blue Pecan\Desktop\Combine Workbooks Example\" Dim FileName As String FileName = Dir(Path & "*.xlsx") Dim ws As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Do While FileName <> "" Workbooks.Open Path & FileName For Each ws In ActiveWorkbook.Sheets ws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) Next ws Workbooks(FileName).Close FileName = Dir() Loop Worksheets(1).Delete Application.ScreenUpdating = False Application.DisplayAlerts = False End Sub
Klik Di Sini untuk Template Excel Gratis
Mendaftar di Universitas Excel Simple Sheet - Kursus Pencicip GRATIS
Semua pembelian membantu mendukung blog ini - terima kasih
Klik Di Sini untuk Membeli Seni Dinding Biasa-biasa saja (& Tato Lainnya) oleh Chester Tugwell
Saat bekerja dengan berbagai sumber data, Anda mungkin sering kesulitan untuk mengompilasi beberapa buku kerja dan lembar kerja sebelum tiba di satu bagian data akhir. Bayangkan sebuah situasi di mana Anda memiliki beberapa ratus buku kerja untuk digabungkan bahkan sebelum Anda dapat memulai hari Anda
Tidak ada yang mau menghabiskan waktu berjam-jam mengerjakan sumber yang berbeda, membuka setiap buku kerja, menyalin dan menempelkan data dari berbagai lembar, sebelum akhirnya membuat satu buku kerja gabungan. Bagaimana jika makro VBA dapat melakukannya untuk Anda?
Dengan panduan ini, Anda dapat membuat kode makro Excel VBA Anda sendiri untuk menggabungkan beberapa buku kerja, semuanya dalam hitungan menit (jika file datanya banyak)
Prasyarat untuk Membuat Kode Makro VBA Anda Sendiri
Anda memerlukan satu buku kerja untuk menampung kode VBA, sedangkan buku kerja data sumber lainnya terpisah. Selain itu, buat satu buku kerja Gabungan untuk menyimpan data gabungan dari semua buku kerja Anda
Buat Konsolidasi folder di lokasi pilihan Anda untuk menyimpan semua buku kerja sumber Anda. Saat makro berjalan, makro akan beralih melalui setiap buku kerja yang disimpan dalam folder ini, menyalin konten dari berbagai lembar, dan menempatkannya di buku kerja Terkonsolidasi
Membuat Kode VBA Excel Anda Sendiri
Setelah prasyarat disingkirkan, sekarang saatnya mempelajari kode dan mulai meretas dasar-dasar untuk menyesuaikannya dengan kebutuhan Anda
Terkait. Fitur Lanjutan Microsoft Excel yang Wajib Anda Ketahui
Tekan tombol Alt+F11 di Excel untuk membuka editor kode makro VBA. Rekatkan kode yang tertulis di bawah ini dan simpan file sebagai buku kerja berkemampuan Makro (. ekstensi xlsm)
Sub openfiles()'declare the variables used within the VBA codeDim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long'disable these functions to enhance code processingWith Application.DisplayAlerts = False.ScreenUpdating = FalseEnd With'change the path of the folder where your files are going to be savedMyFolder = InputBox("Enter path of the Consolidation folder") & "\"'define the reference of the folder in a macro variableMyFile = Dir(MyFolder)'open a loop to cycle through each individual workbook stored in the folderDo While Len(MyFile) > 0'activate the Consolidation workbookWindows("Consolidation").Activate'calculate the last populated rowRange("a1048576").SelectSelection.End(xlUp).SelectActiveCell.Offset(1, 0).Select'open the first workbook within the Consolidation folderWorkbooks.Open Filename:=MyFolder & MyFileWindows(MyFile).Activate'toggle through each sheet within the workbooks to copy the dataDim ws As WorksheetFor Each ws In Sheets ws.Activate ws.AutoFilterMode = False 'ignore the header and copy the data from row 2 If Cells(2, 1) = "" Then GoTo 1 GoTo 101: Next10: Range("a2:az20000").CopyWindows("Consolidation").Activate'paste the copied contentsActiveSheet.PasteWindows(MyFile).Activate'close the open workbook once the data is pastedActiveWorkbook.Close'empty the cache to store the value of the next workbookMyFile = Dir()'open the next file in the folderLoop'enable the disabled functions for future useWith Application.DisplayAlerts = True.ScreenUpdating = TrueEnd WithEnd Sub _Kode VBA Dijelaskan
Bagian pertama kode adalah mendefinisikan subrutin, yang menyimpan semua kode VBA Anda. Tentukan subrutin dengan sub, diikuti dengan nama kodenya. Nama sub bisa apa saja;
Terkait. Situs Web dan Blog untuk Mempelajari Tip dan Trik Excel
Excel VBA memahami variabel yang dibuat pengguna dan tipe data terkait yang dideklarasikan dengan redup (dimensi)
Untuk meningkatkan kecepatan pemrosesan kode, Anda dapat menonaktifkan pembaruan layar dan menekan semua peringatan, karena hal itu akan memperlambat eksekusi kode
Pengguna akan dimintai jalur folder tempat file data disimpan. Lingkaran dibuat untuk membuka setiap buku kerja yang disimpan di dalam folder, menyalin data dari setiap lembar, dan menambahkannya ke buku kerja Konsolidasi
Buku kerja Konsolidasi diaktifkan sehingga Excel VBA bisa menghitung baris yang diisi terakhir. Sel terakhir dalam lembar kerja dipilih, dan baris terakhir dihitung dalam buku kerja menggunakan fungsi offset. Ini sangat berguna, saat makro mulai menambahkan data dari file sumber
Saat loop membuka file sumber pertama, filter dihapus dari setiap lembar tunggal (jika ada), dan data mulai dari A2 hingga AZ20000 akan disalin dan ditempelkan ke buku kerja Konsolidasi
Proses ini diulangi hingga semua lembar buku kerja ditambahkan di dalam buku kerja utama
Terkait. Kursus Online Terbaik untuk Menguasai Excel Tingkat Lanjut
Terakhir, file sumber ditutup setelah semua data ditempelkan. Buku kerja berikutnya dibuka sehingga makro VBA dapat mengulangi langkah yang sama untuk kumpulan file berikutnya
Loop dikodekan untuk dijalankan hingga semua file diperbarui secara otomatis di buku kerja utama
Kustomisasi Berbasis Pengguna
Kadang-kadang, Anda tidak ingin khawatir tentang perintah bawaan, terutama jika Anda adalah pengguna akhir. Jika Anda lebih suka meng-hardcode jalur folder Konsolidasi dalam kode, Anda dapat mengubah bagian kode ini
MyFolder = InputBox("Enter path of the Consolidation folder") & "\"Ke
MyFolder = “Folder path” & "\"Selain itu, Anda juga dapat mengubah referensi kolom, karena langkah tersebut tidak disertakan dalam kode ini. Ganti saja referensi kolom akhir dengan nilai kolom yang terakhir diisi (AZ, dalam hal ini). Perlu Anda ingat bahwa baris terakhir yang terisi dihitung melalui kode makro, jadi Anda hanya perlu mengubah referensi kolom
Untuk memaksimalkan makro ini, Anda bisa menggunakannya hanya untuk menggabungkan buku kerja dalam format yang sama. Jika strukturnya berbeda, Anda tidak dapat menggunakan makro VBA ini
Mengkonsolidasikan Beberapa Buku Kerja Menggunakan Excel VBA Macro
Membuat dan memodifikasi kode VBA Excel relatif mudah, terutama jika Anda memahami beberapa nuansa di dalam kode tersebut. VBA secara sistematis menjalankan setiap baris kode dan mengeksekusinya baris demi baris
Jika Anda membuat perubahan apa pun pada kode, Anda harus memastikan bahwa Anda tidak mengubah urutan kode, karena hal itu akan mengganggu eksekusi kode.