In Microsoft Excel, VBA Macros help to solve various problems and automate repetitive tasks. Now, copying data into another workbook is one such task that we can automate using Excel’s VBA. In this article, you’ll learn 4 useful examples of how to open another workbook and copy data with Excel VBA.
How to Open Another Workbook and Copy Data with Excel VBA: 4 Examples
Here, we have two Excel files, i.e. Copying Data with VBA.xlsm is the Source File containing the data we want to copy. In contrast, the Sales Report.xlsx is the Destination File where we want to paste this copied data.
Now, let’s consider the Quarterly Sales Data in the Source File as shown in the B4:F14 cells. Here, the dataset shows the Location and Quarterly Sales for Q1, Q2, Q3, and Q4 respectively. So, without further delay, let’s dive in!
Additionally, we’ve used Microsoft Excel 365 version, you may use any other version according to your convenience.
Example-1: Opening Another Workbook and Copying Data with VBA
Let’s start with a simple example of copying and pasting data in another workbook using VBA code. It’s simple and easy, just follow these steps.
📌 Step-01: Open Visual Basic Editor
- Firstly, open both the Excel files Copying Data with VBA.xlsm and Sales Report.xlsx.
- Then, in the Source File (Copying Data with VBA.xlsm), navigate to the Developer tab >> click the Visual Basic button.
This opens the Visual Basic Editor in a new window.
📌 Step-02: Insert VBA Code
- Secondly, go to the Insert tab >> select Module.
For your ease of reference, you can copy the code from here and paste it into the window as shown below.
Sub copy_and_paste_data()
'Open workbook from which to copy data
Workbooks.Open ("C:\Users\Eshrak\Downloads\Sales Report.xlsx")
'Copy from source and paste to destination
Workbooks("Copying Data with VBA.xlsm").Worksheets("Dataset").Range("B5:F14").Copy _
Workbooks("Sales Report.xlsx").Worksheets("Sales Data").Range("B5")
End Sub
⚡ Code Breakdown:
Now, I will explain the VBA code used to generate the copy data to another workbook. In this case, the code is divided into 2 steps.
- In the first portion, we have to provide the full path and the proper file extension to open the workbook. In this case, “C:\Users\Eshrak\Downloads\Sales Report.xlsx“.
- In the second portion, copy the B4:F14 range of cells from the Dataset worksheet in the Source File (Copying Data with VBA.xlsm)
- Next, paste it into the Sales Data worksheet inside the Destination File (Sales Report.xlsx).
📌 Step-03: Running VBA Code
- Thirdly, close the VBA window >> save as Macro-Enabled (.xlsm) workbook >> click the Macros button.
This opens the Macros dialog box.
- Following this, select the copy_and_paste_data Macro >> hit the Run button.
📄Note: You have to provide the full path name and the correct file extension, otherwise you’ll run into an error.
Eventually, the results should look like the image given below. Here, I have placed the two files side-by-side to show that the data has been copied over to the other file.
Voila! The data from the Source File has been copied into the Destination File.
Read More: How to Open All Excel Files in a Folder and Copy Data Using VBA
Example-2: Using Excel VBA to Open Another Workbook to Copy Only Values
What if you want to copy the data but paste only the values? Don’t worry, because our next example has you covered! Now, allow me to demonstrate the process in the steps below.
📌 Step-01: Open Visual Basic Editor
- Initially, open both the Excel workbooks Copying Data with VBA.xlsm and Sales Report.xlsx.
- Next, in the Source File (Copying Data with VBA.xlsm), move to the Developer tab >> press the Visual Basic button.
The Visual Basic Editor pops up in a new window.
📌 Step-02: Insert VBA Code
- Then, navigate to the Insert tab >> select Module.
If you want, you may copy the code from here and paste it into the window as shown below.
Sub copy_paste_values()
'Open workbook from which to copy data
Workbooks.Open ("C:\Users\Eshrak\Downloads\Sales Report.xlsx")
'Copy from source and paste to destination
Workbooks("Copying Data with VBA.xlsm").Worksheets("Dataset").Range("B5:F14").Copy
Workbooks("Sales Report.xlsx").Worksheets("Sales Data").Range("B5").PasteSpecial Paste:=xlPasteValues
End Sub
⚡ Code Breakdown:
Here, I’ll explain the VBA code is divided into 2 steps.
- In the first portion, provide the full path and the proper file extension to open the workbook. Here, it is “C:\Users\Eshrak\Downloads\Sales Report.xlsx”
- In the second portion, copy the B4:F14 range of cells from the Dataset worksheet in the Source File (Copying Data with VBA.xlsm)
- In turn, use the Range.PasteSpecial method to paste only the values into the Sales Data worksheet inside the Destination File (Sales Report.xlsx).
📌 Step-03: Running VBA Code
- Following this, close the VBA window >> save as Macro-Enabled (.xlsm) workbook >> click the Macros button.
This opens the Macros dialog box.
- Lastly, select the copy_paste_values Macro >> press the Run button.
📄Note: Please make sure to provide the full path name and the correct file extension, else you’ll run into an error.
Consequently, the results should look like the picture shown below.
Read More: Excel VBA to Loop Through Files in Folder and Copy Data
Example-3: Applying VBA to Open Another Workbook and Copy Data into Next Empty Row
Our third example considers the case where you have some data in a file and you want to copy and paste additional data into the empty cells below. So, let’s see the process in detail.
📌 Step-01: Open Visual Basic Editor and Insert Code
- To begin with, run steps 1 -2 from the previous methods to open the VBA window, insert a new Module, and enter the VBA code.
Sub copy_paste_into_next_empty_row()
Dim WS_Source As Worksheet
Dim WS_Destination As Worksheet
Dim Last_Row As Integer
Dim Last_Row_Position As Integer
'Open the workbook from which to copy the data
Workbooks.Open "C:\Users\Eshrak\Downloads\Copying Data with VBA.xlsm"
'Naming each workbook
Set WS_Source = Workbooks("Copying Data with VBA.xlsm").Sheets("Dataset")
Set WS_Destination = Workbooks("Sales Report.xlsx").Sheets("Sales Data")
'Get last empty row of source workbook
Last_Row = WS_Source.Cells(WS_Source.Rows.Count, "B").End(xlUp).Row
'Get last empty row of destination workbook
Last_Row_Position = WS_Destination.Cells(WS_Destination.Rows.Count, "B").End(xlUp).Row + 1
'Copy from source and paste onto destination
WS_Source.Range("B5:F" & Last_Row).Copy WS_Destination.Range("B" & Last_Row_Position)
End Sub
⚡ Code Breakdown:
Here, the VBA code explanation is divided into 3 steps.
- In the first portion, provide the full path and the proper file extension to open the workbook. Here, it is “C:\Users\Eshrak\Downloads\Copying Data with VBA.xlsm”
- Next, name the two workbooks using the variables WS_Source and WS_Destination.
- In the second portion, obtain the position of the last row in the Source File (Copying Data with VBA.xlsm)
- In turn, get the location of the last row in the Destination File (Sales Report.xlsx) and add 1 to move to the next row.
- In the third portion, copy the data range from the Source File and paste it into the next empty row of the Destination File.
📌 Step-02: Running VBA Code
- Secondly, close the VBA window >> save the spreadsheet as Macro-Enabled (.xlsm) workbook >> click the Macros button.
This opens the Macros dialog box.
- Finally, select the copy_paste_into_next_empty_row Macro >> press the Run button.
Subsequently, the output should look like the screenshot given below.
Read More: Excel VBA to Copy Data from Another Workbook without Opening
Example-4: Clearing Contents and Copying Data into Another Workbook with VBA
For our fourth and final example, we’ll consider the case where you clear the existing data in a file and copy and paste new data into the empty cells. In order to do this, you may follow the below steps.
📌 Step-01: Open Visual Basic Editor and Insert Code
- Similarly, perform steps 1 -2 from the previous methods to open the VBA window, insert a new Module, and enter the VBA code.
Sub clear_previous_data_then_paste()
Dim WS_Source As Worksheet
Dim WS_Destination As Worksheet
Dim LastRow_Source As Long
Dim LastRow_Destination As Long
'Naming each worksheet
Set WS_Source = Workbooks("Copying Data with VBA.xlsm").Worksheets("Dataset")
Set WS_Destination = Workbooks("Sales Report.xlsx").Worksheets("Sales Data")
'Get last empty row of source worksheet
LastRow_Source = WS_Source.Cells(WS_Source.Rows.Count, "B").End(xlUp).Row
'Get last empty row of destination worksheet
LastRow_Destination = WS_Destination.Cells(WS_Destination.Rows.Count, "B").End(xlUp).Offset(1).Row
'Remove previous data from destination worksheet
WS_Destination.Range("B5:F" & LastRow_Destination).ClearContents
'Copy from source and paste onto destination
WS_Source.Range("B5:F" & LastRow_Source).Copy _
WS_Destination.Range("B5")
End Sub
⚡ Code Breakdown:
Now, the VBA code explanation is divided into 4 steps.
- In the first portion, name the two workbooks using the variables WS_Source and WS_Destination.
- In the second portion, obtain the location of the last row in the Source File.
- Likewise, get the position of the last row in the Destination File and use the OFFSET function to move to the next row.
- In the third portion, clear any existing content from the Destination File using the Range.ClearContents method.
- Finally, copy the data range from the Source File and paste it into the next empty row of the Destination File.
📌 Step-02: Running VBA Code
- In turn, close the VBA window >> save the spreadsheet as Macro-Enabled (.xlsm) workbook >> click the Macros button.
This opens the Macros dialog box.
- Lastly, select the clear_previous_data_then_paste Macro >> Press the Run button.
Eventually, your output should look like the screenshot given below.
Read More: Macro to Copy Data from One Workbook to Another Based on Criteria
🔔Things to Remember
Please keep these in mind when using the VBA Macros in this article.
- Make sure to provide the correct file name and the file extension.
- Ensure that both workbooks are open when you’re running the code.
- You don’t have to select any workbook or worksheet range since they are given in the VBA code.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
This article provides the answers on how to open another workbook and copy data with Excel VBA. Make sure to download the practice files. Hope you found it helpful. Please inform us in the comment section about your experience. We are happy to answer your queries. Keep learning and keep growing!