Table of Contents
VBA, or Visual Basic for Applications, is a programming language that allows users to automate tasks in Microsoft Excel. With VBA, it is possible to extract data from one workbook and transfer it to another. This can be done by accessing the source workbook and using specific commands to select and copy the desired data. The extracted data can then be pasted into the destination workbook, making it easily accessible for further analysis or processing. By utilizing VBA, users can efficiently and accurately extract data from multiple workbooks without the need for manual input, saving time and improving productivity.
VBA: 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: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 SubWhen 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.
Cite this article
stats writer (2024). How can I use VBA to extract data from another workbook?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-vba-to-extract-data-from-another-workbook/
stats writer. "How can I use VBA to extract data from another workbook?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-vba-to-extract-data-from-another-workbook/.
stats writer. "How can I use VBA to extract data from another workbook?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-vba-to-extract-data-from-another-workbook/.
stats writer (2024) 'How can I use VBA to extract data from another workbook?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-vba-to-extract-data-from-another-workbook/.
[1] stats writer, "How can I use VBA to extract data from another workbook?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use VBA to extract data from another workbook?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.