Vba menyalin beberapa lembar ke buku kerja baru sebagai nilai

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

Unduh 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

Sub OpenWorkbook()
'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: https://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: https://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 clipboard
  Workbooks("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 clipboard
  Workbooks("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

Bagaimana cara menyalin beberapa lembar di Excel ke VBA buku kerja baru?

Bagaimana cara menyalin beberapa lembar kerja ke dalam buku kerja baru?

Bagaimana cara menyalin data dari beberapa lembar ke satu lembar di Excel VBA?

Bagaimana cara menyalin data dari beberapa lembar ke satu lembar di Excel?