Bagaimana banyak buku kerja bekerja di excel vba?

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.

Bagaimana cara menambahkan beberapa buku kerja Excel di VBA?

Bagaimana cara menggabungkan beberapa buku kerja menjadi satu buku kerja di Excel VBA?

Bagaimana cara mereferensikan beberapa buku kerja di VBA?

Bagaimana cara menarik data dari beberapa buku kerja di Excel VBA?