VBA & Macro Problems

How to Refer to a New Excel Workbook Before SaveAs in VBA

Camila MilosovichCamila Milosovich Nov 10, 2026 869 views

Question details

The user needs to identify and interact with a newly created macro-enabled workbook during data transfer before saving it.

How to Refer to a New Excel Workbook Before SaveAs in VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Declare the Workbook variable

In your VBA module, declare a variable for the workbook by typing: 'Dim newBook As Workbook'.

2
Assign the new workbook to the variable

Create the workbook and immediately assign it to your variable using the Set keyword: 'Set newBook = Workbooks.Add'.

3
Transfer data using the variable reference

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"'.

4
Execute the SaveAs command

When ready to save, call the SaveAs method directly on your variable: 'newBook.SaveAs Filename:="C:\YourPath\NewMacroFile.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled'.

Use an Object Variable to Store the Workbook Reference
Best Practice: Always use object variables rather than relying on ActiveWorkbook, as it makes your VBA code cleaner, faster, and much less prone to runtime errors.

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. 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
  2. 2. Access the Developer Tab: Navigate to the top ribbon and click on the 'Developer' tab to access macro tools.
  3. 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.
High compatibility with Microsoft Excel (.xlsm) macro-enabled formatsBuilt-in Visual Basic Editor with a familiar interfaceLightweight installation and fast execution of automated scriptsFree and robust alternative for everyday spreadsheet automation
microsoft office alternative - wps office

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.