VBA: How to Extract Data from Another Workbook

You can use the following syntax in VBA to extract data from another workbook:

Sub ExtractData()
    
    'turn off screen updates to make this run faster
    Application.ScreenUpdating = False
    
    'specify workbook we want to extract data from
    Set wb = Workbooks.Open("C:\Users\bobbi\OneDrive\Desktop\my_data.xlsx")
    
    'extract all data from Sheet1 and paste as new sheet in current workbook
    wb.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(1)
    
    'do not save any changes to workbook we extracted data from
    wb.Close SaveChanges:=False
    
    'turn screen updating back on
    Application.ScreenUpdating = True
    
End Sub

This particular macro opens the workbook called my_data.xlsx located in the following location:

C:\Users\Bob\Desktop\my_data.xlsx

It then copies all of the data from the sheet called Sheet1 in that workbook and copies it to the sheet directly after the first sheet in our currently active workbook.

Note: The line Application.ScreenUpdating = False tells VBA to run this process in the background so that it runs quicker.

The following example shows how to use this syntax in practice.

Example: Extract Data from Another Workbook Using VBA

Suppose we have the following Excel workbook open and we’re viewing it:

Now suppose we have another workbook that is closed but contains the following data:

We can use the following macro to extract the data from Sheet1 of this closed workbook and paste it into a new sheet directly after the first sheet in our currently opened workbook:

Sub ExtractData()
    
    'turn off screen updates to make this run faster
    Application.ScreenUpdating = False
    
    'specify workbook we want to extract data from
    Set wb = Workbooks.Open("C:\Users\bobbi\OneDrive\Desktop\my_data.xlsx")
    
    'extract all data from Sheet1 and paste as new sheet in current workbook
    wb.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(1)
    
    'do not save any changes to workbook we extracted data from
    wb.Close SaveChanges:=False
    
    'turn screen updating back on
    Application.ScreenUpdating = True
    
End Sub

When we run this macro, we receive the following output: