Table of Contents
Using VBA, you can extract data from another workbook by creating a macro that opens the workbook, selects the data you want to extract, and then copies and pastes the data into the current workbook. This macro can be run manually or you can set it to run automatically at a certain time. Macros can be created by creating a new module and writing the code required to accomplish the task. Once the code has been written, it can be saved and assigned to a button or other control to initiate the macro.
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:UsersbobbiOneDriveDesktopmy_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:UsersBobDesktopmy_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:UsersbobbiOneDriveDesktopmy_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:
Notice that the data from the closed workbook has been extracted and pasted as a new sheet into our currently active workbook.