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

You have two Excel files, Copying Data with VBA.xlsm is the Source File containing the data you want to copy, and Sales Report.xlsx is the Destination File where data will be pasted.

Excel Files

Consider the Quarterly Sales Data in the Source File as shown in B4:F14 . Here, the dataset shows the Location and Quarterly Sales for Q1, Q2, Q3, and Q4.

Dataset

Example 1 – Opening Another Workbook and Copying Data with VBA

Step 1: Open the Visual Basic Editor

  • Open both Excel files Copying Data with VBA.xlsm and Sales Report.xlsx. 
  • In the Source File (Copying Data with VBA.xlsm), go to the Developer tab >> click Visual Basic.

Using VBA Code

The Visual Basic Editor will open.

Step 2: Insert the VBA Code

  • Go to the Insert tab >> select Module.

Using VBA Code

  • Enter the code 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

How to Open Another Workbook and Copy Data with Excel VBA Using Simple Copy Paste

Code Breakdown:

  • The full path and the proper file extension to open the workbook are provided. Here, “C:\Users\Eshrak\Downloads\Sales Report.xlsx“.
  • B4:F14 is copied from the Source File (Copying Data with VBA.xlsm)
  • It is pasted in the Destination File (Sales Report.xlsx).

Code Explanation

Step 3: Run the VBA Code

  • Close the VBA window >> save as Macro-Enabled (.xlsm) >> click Macros.

In the Macros dialog box:

  • Select copy_and_paste_data >> Click Run.

Note: You have to provide the full path name and the correct file extension.

How to Open Another Workbook and Copy Data with Excel VBA Using Simple Copy Paste

This is the output.

How to Open Another Workbook and Copy Data with Excel VBA Using Simple Copy Paste

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 Values Only

Step 1: Open the Visual Basic Editor

  • Open both Excel workbooks: Copying Data with VBA.xlsm and Sales Report.xlsx. 
  • In the Source File (Copying Data with VBA.xlsm), go to the Developer tab >> choose Visual Basic.

Using VBA Code

The Visual Basic Editor window will open.

Step 2: Insert the VBA Code

  • Go to the Insert tab >> select Module.

How to Open Another Workbook and Copy Data with Excel VBA Using Paste Values Only

  • Enter this code.
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

How to Open Another Workbook and Copy Data with Excel VBA Using Paste Values Only

Code Breakdown:

  • The full path and the proper file extension to open the workbook are provided. Here, “C:\Users\Eshrak\Downloads\Sales Report.xlsx
  • B4:F14 is copied in the Source File (Copying Data with VBA.xlsm)
  • The Range.PasteSpecial function pastes values only into the Destination File (Sales Report.xlsx).

How to Open Another Workbook and Copy Data with Excel VBA Using Paste Values Only

Step 3: Run the VBA Code

  • Close the VBA window >> save as Macro-Enabled (.xlsm) >> click Macros.

In the Macros dialog box:

  • Select copy_paste_values >> Click Run.

Note: Provide the full path name and the correct file extension.

How to Open Another Workbook and Copy Data with Excel VBA Using Paste Values Only

This is the output.

How to Open Another Workbook and Copy Data with Excel VBA Using Paste Values Only

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 the Next Empty Row

Step 1: Open the Visual Basic Editor and Insert the Code

  • Follow steps 1 and 2 in Example 2 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

Using VBA Code

Code Breakdown:

  • The full path and the proper file extension to open the workbook are provided. Here, “C:\Users\Eshrak\Downloads\Copying Data with VBA.xlsm
  • The two workbooks are named using the variables WS_Source and WS_Destination.
  • The position of the last row in the Source File (Copying Data with VBA.xlsm) is obtained.
  • The location of the last row in the Destination File (Sales Report.xlsx) is determined and 1 is added to move to the next row.
  • The data range from the Source File is copied and pasted into the next empty row of the Destination File.

How to Open Another Workbook and Copy Data with Excel VBA Pasting into Next Row

Step 2: Run the VBA Code

  • Close the VBA window >> save the spreadsheet as Macro-Enabled (.xlsm)  >> click Macros.

The Macros dialog box will open.

  • Select copy_paste_into_next_empty_row  >> Click Run.

How to Open Another Workbook and Copy Data with Excel VBA Pasting into Next Row

This is the output.

How to Open Another Workbook and Copy Data with Excel VBA Pasting into Next Row

Read More: Excel VBA to Copy Data from Another Workbook without Opening


Example 4 – Clearing Content and Copying Data into Another Workbook with VBA

Step 1: Open the Visual Basic Editor and Insert the Code

  • Follow steps 1 and 2 in Example 3 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

Using VBA Code

Code Breakdown:

  • The two workbooks are named using the variables WS_Source and WS_Destination.
  • The location of the last row in the Source File is obtained.
  • The position of the last row in the Destination File is determined and the OFFSET function is used to move to the next row.
  • Any existing content is cleared from the Destination File using Range.ClearContents.
  • The data range is copied from the Source File and pasted into the next empty row of the Destination File.

How to Open Another Workbook and Copy Data with Excel VBA Clearing Contents and Pasting

Step 2: Run the VBA Code

  • Close the VBA window >> save the spreadsheet as Macro-Enabled (.xlsm) >> click Macros.

In the Macros dialog box:

  • Select clear_previous_data_then_paste>> Click Run.

How to Open Another Workbook and Copy Data with Excel VBA Clearing Contents and Pasting

This is the output.

How to Open Another Workbook and Copy Data with Excel VBA Clearing Contents and Pasting

Read More: Macro to Copy Data from One Workbook to Another Based on Criteria


Things to Remember

  • 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

Practice here.

Practice Section


Download Practice Workbook

You can download the practice workbook from the link below.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo