How to Refer to a New Excel Workbook Before SaveAs in VBA
Question details
The user needs to identify and interact with a newly created macro-enabled workbook during data transfer before saving it.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transferring data using a VBA macro to a newly created workbook before calling the SaveAs method.
- Observed behavior
- The user requires a reliable VBA coding method to reference the newly generated active workbook so that data can be correctly pasted and saved.
Ensure that your Developer tab is enabled in the ribbon and that you have a basic understanding of declaring object variables in the Visual Basic Editor.
Use an Object Variable to Store the Workbook Reference
The most reliable way to reference a new workbook throughout your macro is to assign it to an object variable exactly at the moment it is created.
By setting a workbook variable to 'Workbooks.Add', you create a direct link to that specific file in your system's memory. This prevents errors that occur when the active window changes unexpectedly during data transfer.
In your VBA module, declare a variable for the workbook by typing: 'Dim newBook As Workbook'.
Create the workbook and immediately assign it to your variable using the Set keyword: 'Set newBook = Workbooks.Add'.
Use the variable to interact with the new workbook directly, regardless of which window is active. For example: 'newBook.Sheets(1).Range("A1").Value = "Transferred Data"'.
When ready to save, call the SaveAs method directly on your variable: 'newBook.SaveAs Filename:="C:\YourPath\NewMacroFile.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled'.

Capture the Active Window Caption
If your macro design prevents you from declaring a workbook variable immediately, you can capture the newly generated window's caption text while it is still active.
Write and Execute VBA Macros Seamlessly in WPS Office
WPS Office Spreadsheet offers robust support for VBA and Macros. You can easily automate tasks, declare object variables, and manage multiple workbooks just as you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
- 2. Access the Developer Tab: Navigate to the top ribbon and click on the 'Developer' tab to access macro tools.
- 3. Open the Visual Basic Editor: Click the 'Visual Basic' icon to launch the editor, where you can paste or write your 'Workbooks.Add' automation scripts.

Frequently Asked Questions
Why do I get an 'Object Required' error when assigning a new workbook?
This happens when you forget to use the 'Set' keyword in VBA. Because a workbook is an object, you must write 'Set newBook = Workbooks.Add', rather than just 'newBook = Workbooks.Add'.
Can I just reference the new workbook by its default name like 'Book1'?
It is highly discouraged. Excel names new workbooks sequentially based on the current session (e.g., Book1, Book2, Book3). Hardcoding 'Book1' will cause your macro to crash if Book1 already exists or if the new workbook is assigned a different number.
How do I ensure the new workbook is saved as a macro-enabled file (.xlsm)?
When using the SaveAs method in VBA, you must specify the correct file format. Use the parameter 'FileFormat:=xlOpenXMLWorkbookMacroEnabled' (or the numeric equivalent 52) alongside your '.xlsm' filename.




