Intinya. Pelajari cara menggunakan makro VBA untuk menyalin & menempelkan data dari satu buku kerja Excel ke buku kerja Excel lainnya, termasuk menambahkan data ke bagian bawah rentang yang ada atau mengganti data
Tingkat keahlian. Intermediat
Video tutorial
Tonton di YouTube & Berlangganan ke Saluran kamiUnduh File Excel
Ikuti video di atas menggunakan file Excel yang sama dengan yang saya gunakan. Anda dapat mengunduhnya dengan mengklik di bawah ini. Inilah buku kerja tempat saya menyalin data dari contoh saya
Data Baru. zip Unduh
Dan inilah buku kerja tempat saya menyalin data. Ini adalah salah satu yang memiliki semua kode makro di dalamnya
Laporan. zip Unduh
Salin Data dari Satu Buku Kerja ke Buku Kerja Lainnya Menggunakan Excel Macros
Ada beberapa cara untuk menyalin & menempel data dengan VBA. Pertama kita akan menggunakan Range. Metode salin. Ini memungkinkan kita untuk melakukan seluruh tindakan dalam satu baris kode
Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _Workbooks("Reports.xlsm").Worksheets("Data").Range("A2")
Jangkauan. Metode salin memiliki parameter Destination opsional yang memungkinkan kita menentukan rentang yang ingin kita tempel
Kami mereferensikan buku kerja sumber, lembar kerja, dan rentang yang ingin kami salin. Untuk parameter Tujuan, kami mereferensikan buku kerja tujuan, lembar kerja, dan sel awal rentang yang akan ditempelkan
Jangkauan. Metode salin melakukan salin dan tempel biasa yang mencakup pemformatan dan rumus. Jika Anda hanya ingin menempelkan nilai, ada contoh di bawah ini
Poin Penting untuk Diingat
Saat menggunakan makro ini untuk menyalin data dari satu buku kerja ke buku kerja lainnya, perhatikan poin-poin ini
- Anda harus mereferensikan ekstensi file yang benar di properti Buku Kerja (lihat video di atas untuk detailnya)
- Buku kerja tidak harus diaktifkan makro agar ini berfungsi
- Kode ini dapat disimpan dalam buku kerja terpisah, seperti Buku Kerja Makro Pribadi Anda, jika Anda mau. (Pelajari cara membuat Buku Kerja Makro Pribadi di sini. )
- Anda tidak perlu memilih atau mengaktifkan buku kerja, lembar kerja, atau bahkan rentang terlebih dahulu. Ini karena kode sudah menentukan detail tersebut
- Kedua buku kerja harus terbuka saat menggunakan kode ini. Namun proses membuka dan menutup buku kerja bisa diotomatisasi dengan lebih banyak kode
'Open a workbook
'Open method requires full file path to be referenced.
Workbooks.Open "C:\Users\username\Documents\New Data.xlsx"
'Open method has additional parameters
'Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
'Help page: //docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open
End Sub
Sub CloseWorkbook()
'Close a workbook
Workbooks("New Data.xlsx").Close SaveChanges:=True
'Close method has additional parameters
'Workbooks.Close(SaveChanges, Filename, RouteWorkbook)
'Help page: //docs.microsoft.com/en-us/office/vba/api/excel.workbook.close
End Sub_
TempelMetode Khusus untuk Menempelkan Nilai, Format, dll
Saat menempelkan data ke buku kerja tujuan menggunakan VBA, Anda juga dapat menggunakan salah satu fitur biasa Tempel Spesial
Ada contoh makro di bawah ini. Anda akan melihat bahwa contoh saya menggunakan tipe PasteValues, tetapi Anda juga dapat menggunakan PasteFormulas, PasteFormats, atau opsi PasteSpecial lainnya yang tersedia. Berikut adalah daftar PasteTypes
'Copy range to clipboardWorkbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
Untuk mempelajari lebih lanjut tentang opsi TempelSpesial, lihat seri video saya tentang Salin dan Tempel dengan VBA
Menempelkan Di Bawah Sel Terakhir
Terkadang ukuran rentang data Anda di file sumber dan tujuan akan berubah setiap kali Anda menjalankan makro. Misalnya, Anda mungkin memiliki tugas harian untuk menambahkan entri baru dari lembar yang diekspor ke daftar master di buku kerja lain
Dalam hal ini, Anda ingin menambahkan entri baru tepat di bawah entri terakhir pada lembar tujuan Anda. Untuk melakukannya, Anda dapat menggunakan makro berikut
Sub Copy_Paste_Below_Last_Cell()'Find the last used row in both sheets and copy and paste data below existing data.
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
'Set variables for copy and destination sheets
Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("A2:D" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)
Kode ini menempelkan data sumber Anda tepat di bawah data lembar tujuan yang ada
Selain menemukan baris terakhir dalam rentang atau lembar, Anda juga dapat menemukan kolom atau sel terakhir. Lihat postingan dan video saya tentang 3 cara menemukan baris atau kolom yang terakhir digunakan untuk mempelajari lebih lanjut
Menghapus Rentang Tujuan Sebelum Menempel
Daripada menambahkan ke daftar dalam rentang tujuan, Anda mungkin lebih suka menghapus rentang yang ada sebelum menempelkan data baru. Anda dapat melakukannya dengan makro ini
Sub Clear_Existing_Data_Before_Paste()Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Clear contents of existing data range
wsDest.Range("A2:D" & lDestLastRow).ClearContents
'4. Copy & Paste Data
wsCopy.Range("A2:D" & lCopyLastRow).Copy _
wsDest.Range("A2")
End Sub
_
Menjalankan makro itu akan menghapus semua data yang ada di rentang tujuan sebelum memasukkan data dari lembar kerja sumber
Kode Alternatif untuk Menyalin Data ke Buku Kerja Anda Saat Ini
Saya juga ingin menyajikan kepada Anda opsi yang sedikit berbeda untuk makro Anda. Alih-alih mengidentifikasi buku kerja tujuan berdasarkan nama, Anda bisa menggunakan properti ThisWorkbook. Ini bisa dilakukan selama makro disimpan di buku kerja tujuan (atau sumber).
Dengan melakukan ini, Anda tidak perlu mengubah kode jika Anda mengubah nama file untuk buku kerja tujuan Anda. Berikut adalah kode VBA yang menggunakan ThisWorkbook
Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _ThisWorkbook.Worksheets("Data").Range("A2")
Ini mengingatkan saya bahwa VBA akan selalu berasumsi bahwa makro yang ingin Anda jalankan berlaku untuk buku kerja yang aktif jika Anda tidak menentukan buku kerja di setiap baris kode. Saya berbicara tentang asumsi kritis tersebut dan poin penting lainnya tentang menjalankan kode VBA dalam video ini tentang Asumsi VBA
Salin Tempel Antar Lembar di Buku Kerja yang Sama
Anda dapat mengubah salah satu contoh di atas untuk menyalin & menempel antar lembar dalam buku kerja yang sama. Cukup gunakan referensi buku kerja yang sama untuk salinan dan rentang tujuan. Ini sebuah contoh
'Copy range to clipboardWorkbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("New Data.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
_
Anda tidak selalu perlu menentukan buku kerja, tetapi itu adalah kebiasaan yang baik untuk dilakukan. Jika tidak, VBA membuat asumsi yang dapat membuat Anda mendapat masalah
Kesimpulan
Saya harap tips dan makro ini membantu menghemat waktu Anda saat menyalin data antar buku kerja. Mengotomatiskan tugas yang membosankan ini akan membantu mencegah kesalahan dan memudahkan orang lain memperbarui laporan Anda