If you are working with different Excel files that contain the same type of information i.e, information of the employees from different departments of a large organization, then you might need to copy the information from all these Excel files to one single file. But opening each file and copying the information to another one might be tiresome if you have a lot of files to process. In this tutorial, I will show you how to use Excel VBA to get cell value from another workbook without opening it.
Download Practice Workbook
Download this practice books to exercise the task while you are reading this article.
2 Easy Steps to Use VBA to Get Cell Value from Another Workbook without Opening in Excel
Let’s assume a scenario where we have an Excel worksheet that has the information about the employees of an organization. We have named this workbook as the Source workbook and named the one Destination where we will copy the cell values to. We will copy the cell values of range B2:E10 of the Source workbook while it is closed to the Destination workbook using VBA. The image below shows the cell values of the Source workbook.
Step 1: Open the Visual Basic Window from the Developer Tab in Excel
- First, we will select Visual Basic from the Developer. We can also press ALT+F11 to open it.
- Now, click on the Insert button and select Module.
- Excel VBA: R1C1 Formula with Variable (3 Examples)
- How to Reference Text in Another Cell in Excel (14 Ways)
- Excel VBA Examples with Cell Reference by Row and Column Number
- [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)
- Excel VBA: Insert Formula with Relative Reference (All Possible Ways)
Step 2: Insert the VBA Code to Get Cell Value from Another Workbook
- Then, write down the following code in the window that appears.
Sub get_cell_value() Application.ScreenUpdating = False Set Source_workbook = Workbooks.Open("C:\Users\User\OneDrive\Desktop\VBA\Source.xlsx") Workbooks("Source").Worksheets("Sheet1").Range("B2:E10").Copy _ Workbooks("Destination").Worksheets("Sheet1").Range("B2:E10") Source_workbook.Close SaveChanges:=False Application.ScreenUpdating = True End Sub
- This will create a sub-procedure named get_cell_value.
Application.ScreenUpdating = False
- This code above will disable the Application.ScreenUpdating to run the process in the background.
Set Source_workbook = Workbooks.Open("C:\Users\User\OneDrive\Desktop\VBA\Source.xlsx")
- Will open the Source workbook.
Workbooks("Source").Worksheets("Sheet1").Range("B2:E10").Copy _ Workbooks("Destination").Worksheets("Sheet1").Range("B2:E10")
- The code line above will copy the cell values of the range B2:E10 from the Source workbook and paste them into the Destination workbook.
- Finally, this will close the source workbook.
- Now, click on the Run (▶).
- If a window named Macro appears, just click on Run from that window.
- Finally, we will see that cell values of range B2:E10 of the Source worksheet have been copied to the Destination
Read More: Cell Reference in Excel VBA (8 Examples)
- You should have the Outlook mail app to paste the range into the email body using VBA.
- If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.
- To open the VBA editor Press ALT + F11. You can press ALT + F8 to bring up the Macro window.
In this article, we have learned how to use Excel VBA to get cell value from another Workbook without opening it. I hope from now on you can use VBA code to get cell value from another Workbook without opening it. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
- Different Types of Cell References in Excel (With Examples)
- Difference Between Absolute and Relative Reference in Excel
- Absolute Reference in Excel (With Examples)
- Excel Sheet Name in Formula Dynamic (3 Approaches)
- Reference Another Sheet in Excel (3 Methods)
- How to Lock a Cell in Excel Formula (2 Ways)