How to Reference Another Excel Workbook in VBA Without Selecting It
Question details
The user needs to write data to a specific cell in a different open workbook using a VBA macro, bypassing the need to select or activate the target workbook first.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Executing a VBA macro in a primary workbook (e.g., 'Budgets') to manipulate or input data into a secondary open workbook (e.g., 'Actuals').
- Observed behavior
- The user is looking for the correct VBA syntax to interact with another workbook directly in the background without bringing it to the active window.
Ensure both workbooks are open in the same instance of the application, and verify the exact file name (including the extension, like .xlsx) and worksheet name of your target workbook.
Use a Fully Qualified Reference Directly
Directly specify the target workbook, worksheet, and range in a single line of code to improve macro performance and reliability.
In VBA, you do not need to mimic user actions like clicking or selecting. Using the .Select or .Activate methods slows down your code and makes it prone to errors. Instead, you can construct a 'fully qualified reference' that tells VBA exactly where the data should go in the object hierarchy.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor.
In the Project Explorer pane on the left, find your active workbook (e.g., the 'Budgets' workbook). Right-click it, select 'Insert', and choose 'Module'.
Inside your macro sub-routine, use the Workbooks collection to target the destination directly. For example, to write the value 100 to cell A1 on the 'Jan' sheet of 'Actuals.xlsx', type: Workbooks("Actuals.xlsx").Worksheets("Jan").Range("A1").Value = 100
Press F5 to run the macro. The value will be written to the 'Actuals' workbook instantly without changing your active view.

Assign Workbook and Worksheet to Object Variables
Declare and set variables for your workbooks and worksheets. This is highly recommended for reusable code and complex macros that interact with the target workbook multiple times.
Run Your VBA Macros Seamlessly in WPS Office
WPS Office offers comprehensive support for VBA macros in its Spreadsheet application. You can seamlessly run, edit, and manage your existing Excel scripts—including cross-workbook references—without changing a single line of code.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbooks you wish to automate.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macros', or simply press ALT + F11 to open the VBA Editor.
- 3. Insert Your Code: Add a new Module and paste your fully qualified workbook references or object variables.
- 4. Execute the Script: Run your macro to instantly update data across your workbooks in the background.

Frequently Asked Questions
Why should I avoid using Select or Activate in VBA?
Using .Select or .Activate forces the application to physically update the screen and change the active focus, which significantly slows down macro execution and causes screen flickering. It also makes your code more prone to breaking if the active window changes unexpectedly while the macro is running.
What happens if the referenced workbook is closed?
If you try to reference a workbook that is not currently open, VBA will return a Run-time error 9: 'Subscript out of range'. To fix this, you must either ensure the workbook is open before running the macro, or add a line of code to dynamically open it using Workbooks.Open("C:\Path\To\Actuals.xlsx").
Can I reference a workbook dynamically instead of hardcoding its name?
Yes. Instead of typing the exact name, you can assign dynamic references. For example, you can use 'ThisWorkbook' to always refer to the workbook where the VBA code is stored, or 'ActiveWorkbook' for the currently focused file. You can also loop through the Workbooks collection to find a file dynamically based on certain criteria.




