TL;DR: Unleash the power of VBA macros to seamlessly copy and paste data between Excel workbooks. We’ll cover adding data to existing tables and even replacing entire datasets.
Skill Level: Intermediate
Video Tutorial
Grab the Excel Files
Want to follow along? Download the same Excel files used in the video to practice these techniques.
Copying Data Between Workbooks Using Excel Macros
VBA offers several ways to copy and paste data. We’ll start with the Range.Copy method, known for its efficiency. You can often perform the entire copy-paste operation with just a single line of code.
The Range.Copy method has a neat trick up its sleeve: the optional Destination parameter. This allows you to specify exactly where you want the data to be pasted.
You’ll need to specify the source workbook, worksheet, and the range you want to copy from. Then, for the Destination parameter, reference the destination workbook, worksheet, and the top-left cell of the range where you want to paste the data.
Range.Copy performs a complete copy, including formatting and formulas. If you only want to paste values, check out the example further down.
Key Considerations
Keep these points in mind when copying data between workbooks with VBA:
- Double-check the file extension in the
Workbooksproperty (see the video for details). - The workbooks *don’t* need to be macro-enabled for this to work.
- The code can live in a separate workbook, like your Personal Macro Workbook.
- You don’t need to select or activate workbooks, worksheets, or ranges. The code specifies everything.
- Both workbooks must be open. You *can* automate opening/closing with more code.
The Power of PasteSpecial
VBA also allows you to use Excel’s standard Paste Special options.
The example below uses PasteValues, but you can use PasteFormulas, PasteFormats, or any of the other PasteSpecial options. Microsoft has a list of the PasteTypes.
Appending Data: Pasting Below the Last Cell
Sometimes, the amount of data in your source and destination files changes. Think daily tasks where you’re adding new entries from an export to a master list.
In this case, you’ll want to append the new entries *directly below* the last entry on your destination sheet. Use the following macro:
This code pastes your source data right after the existing data on the destination sheet.
Clearing the Destination Before Pasting
Rather than adding to an existing list, you might want to clear the destination range before pasting. Here’s the macro for that:
This macro *removes* all existing data in the destination before pasting the new data.
Alternative: Referencing the Current Workbook
Instead of identifying the destination by name, use the ThisWorkbook property. This only works if the macro lives inside either the destination or the source workbook.
This way, you don’t have to change the code if the destination file name changes. Here’s the code:
Remember that VBA assumes a macro applies to the *active* workbook if you don’t specify one in each line of code. It’s a critical assumption to be aware of.
Copying Between Sheets in the Same Workbook
Modify any of the above examples to copy and paste between sheets within the same workbook. Just use the same workbook reference for the copy and destination ranges.
You might not always *need* to specify the workbook, but it’s a good habit. VBA’s assumptions can cause trouble.