How to Open Another Workbook and Copy Data with Excel VBA

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.

Download Practice Workbook

You can download the practice workbook from the link below.

4 Examples to Open Another Workbook and Copy Data with Excel VBA

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

📌 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 Folder and Select File Using Excel VBA (4 Examples)

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

📌 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: How to Open Workbook from Path Using Excel VBA (4 Examples)

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

📌 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: How to Open Workbook and Run Macro Using VBA (4 Examples)

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

📌 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: How to Open Workbook with Variable Name with Excel VBA

🔔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.

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, the Exceldemy team, are happy to answer your queries. Keep learning and keep growing!

Related Articles

• How to Open Workbook as Read-Only with Excel VBA
• Browse for File Path Using Excel VBA (3 Examples)
• How to Open File Dialog Default Folder with Excel VBA (3 Ways)
• [Fixed!] Method Open of Object Workbooks Failed (4 Solutions)
• Excel VBA to Open Workbook in Background (2 Useful Examples)