VBA Create New Workbook With Name: A Practical Guide

In Excel VBA, create a workbook with Workbooks.Add, then give it a lasting name and location with SaveAs. For example, create a workbook and save it as Monthly Report.xlsx in Excel’s default folder; the name is set when the file is saved, not when the blank workbook is added.

Create a Workbook and Save It With a Name

This example creates a new workbook and saves it in the folder Excel reports as its default file location. It uses the standard .xlsx format, which does not store VBA code inside the workbook.

Sub CreateNamedWorkbook()
    Dim newWb As Workbook
    Dim fullPath As String

    ' Build a path in Excel's default file location.
    fullPath = Application.DefaultFilePath & Application.PathSeparator & "Monthly Report.xlsx"

    ' Add a workbook, then save it with the chosen name and format.
    Set newWb = Workbooks.Add
    newWb.SaveAs Filename:=fullPath, FileFormat:=xlOpenXMLWorkbook
End Sub

Run the macro, and Excel creates the workbook and saves it at that path. Change the text Monthly Report.xlsx to the filename you need. Before running any macro, save a copy of your existing workbook: macros cannot be undone with Ctrl+Z. If a file with the same name already exists, Excel may ask whether to replace it; do not confirm unless overwriting is intended.

Why Add and SaveAs Are Separate Steps

Workbooks.Add makes a new workbook in the current Excel session. It does not, by itself, save a file to disk. The workbook is usually shown with a temporary name such as Book1 until it is saved. Use SaveAs to choose its file name, folder, and file format.

In VBA, a workbook’s Name is not a setting you assign to rename an unsaved workbook; it reflects the file name after saving. Its Path property is blank before the workbook has been saved, while FullName represents the full file name and path after saving. This distinction explains why code that only adds a workbook does not create a named file on the computer.

A saved workbook can later be renamed in File Explorer or with another SaveAs call. Saving under a different name can leave both the original file and the newly named file on disk. If you want to change the existing file rather than create a second copy, use Save after the first save.

Choose the Folder and File Name

The Filename argument can contain just a filename or a full path. A full path is usually more dependable because it makes the destination explicit. For example, on Windows a full path could look like C:\Reports\Monthly Report.xlsx, provided the Reports folder already exists. VBA does not automatically create a missing folder when you call SaveAs.

To let someone choose the destination interactively, use Excel’s Save As dialog rather than guessing a folder. One practical route is to create the workbook and then let the user save it through the Excel interface. If a macro must save automatically, decide in advance whether the destination is a fixed folder, the workbook’s folder, or a user-selected folder. A workbook that has never been saved has no existing folder to use as its own location.

Use a full path when the macro must put the file in a particular known folder.
Check that the destination folder exists and that the user has permission to write there.
Choose a clear filename and include the extension that matches the selected file format.
Avoid Windows filename characters such as \ / : * ? " < > |, and avoid names that end with a period or space.

Folder paths differ by user and computer. A path that works on one Windows installation may not exist on another, and Mac paths are formatted differently. For a macro shared across computers, a dialog or a configurable destination is safer than hard-coding a personal folder.

Select the Correct Excel File Format

The file extension and the FileFormat argument should agree. For an ordinary workbook, use .xlsx with xlOpenXMLWorkbook. If the new workbook needs to contain VBA code, use .xlsm with xlOpenXMLWorkbookMacroEnabled. Saving a macro-enabled workbook as .xlsx removes VBA project content from that file, so do not choose .xlsx when the workbook itself must retain macros.

.xlsx: Standard Excel workbook; appropriate for worksheets, formulas, and charts without embedded VBA.
.xlsm: Macro-enabled workbook; use it when VBA code needs to be stored in the workbook.
.xls: Older Excel 97–2003 format; use only when compatibility with older systems is required.

Creating a workbook from a macro does not automatically copy the macro into the new workbook. The code may be running from another workbook, an add-in, or the Personal Macro Workbook. If the new file should contain its own code, save it in a macro-enabled format and make sure the code is actually placed in its VBA project. The format alone does not transfer code from the workbook that ran the macro.

Use a Duplicate-Name Check

When a macro runs repeatedly, it may encounter a file with the same name. A safer workflow checks for that file before creating and saving the workbook, then stops rather than silently replacing anything. This version uses the same default folder as the first example.

Sub CreateNamedWorkbookIfAvailable()
    Dim newWb As Workbook
    Dim fullPath As String

    ' Set the destination and stop if the file already exists.
    fullPath = Application.DefaultFilePath & Application.PathSeparator & "Monthly Report.xlsx"
    If Len(Dir$(fullPath)) > 0 Then
        MsgBox "That file already exists: " & fullPath, vbExclamation
        Exit Sub
    End If

    ' Create and save the workbook only when the name is available.
    Set newWb = Workbooks.Add
    newWb.SaveAs Filename:=fullPath, FileFormat:=xlOpenXMLWorkbook
End Sub

This check prevents the macro from intentionally saving over an existing file, but it is not a complete file-management system. Another process could create a file after the check and before the save, and the macro does not create missing folders or handle every possible save error. For important files, keep backups and consider adding error handling appropriate to the workflow rather than turning off Excel alerts globally.

Control What the New Workbook Contains

Workbooks.Add can make a new workbook using Excel’s current default number of sheets, so the result may contain more than one blank sheet. The number depends on Excel settings. If the workbook needs a specific starting layout, add or remove sheets after creating it, or supply a supported template argument to Workbooks.Add. A template is useful when each new file should begin with prepared formatting, named ranges, formulas, or page settings.

For instance, a macro can assign a worksheet name after creation, populate cells, and then save the workbook. Worksheet names and workbook filenames are different things: changing a tab from Sheet1 to Summary does not rename the saved file. Likewise, saving the workbook as Summary.xlsx does not automatically rename a worksheet tab.

Be deliberate about the active workbook. When a macro creates another workbook, Excel makes the new one active, but code is clearer and less error-prone when it refers to the saved workbook through a variable such as newWb. That way, later instructions can target the intended file instead of whichever workbook happens to be active after a dialog, event, or other action.

Run the Macro and Account for Platform Differences

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure there. Run it from the editor with F5, or return to Excel and use Alt+F8 to choose a macro. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.

Excel for Microsoft 365 on Mac also supports VBA, though some Windows-specific automation and file paths may not work unchanged. The Visual Basic Editor is available from the Developer tab or Excel’s Visual Basic command; the keyboard shortcut may require Option+F11 or Fn+Option+F11 depending on the Mac keyboard. The Windows File > Options path does not apply on Mac, where ribbon customization is in Excel > Preferences or Settings, depending on the release. Use a Mac-appropriate folder path, and check any platform-specific file access permissions.

Excel for the web can open and edit many workbook files but cannot create or run VBA macros. Open the file in the desktop Excel app to use this code. Also, only run macros from sources you trust: a macro can make changes to files and data, and Excel’s security settings may block downloaded or untrusted VBA projects.

Common Problems and How to Fix Them

The workbook appears as Book1: The macro added it but did not successfully save it. Check that the code reaches SaveAs and that the destination is valid.
Excel says the path cannot be found: Create the folder first or change the path to an existing folder. Confirm separators and permissions for the operating system in use.
A replacement prompt appears: A file with that name may already exist. Confirm only if replacing it is intended; otherwise choose a different name or use a duplicate check.
The workbook loses macros: The file may have been saved as .xlsx. Use .xlsm with xlOpenXMLWorkbookMacroEnabled when the workbook must store VBA.
The macro does not run: Check that macros are permitted by the organization’s security policy and that the code is in a standard module in a macro-enabled source file or another accessible VBA project.

Do not solve every prompt by setting Application.DisplayAlerts = False. Disabling alerts can suppress warnings beyond the one you meant to handle and may allow an overwrite without the expected confirmation. If a macro must alter this application setting, save its original value and restore it even when an error occurs.

Frequently Asked Questions

How do I create a new workbook in VBA?
Use Set newWb = Workbooks.Add, with newWb declared as a Workbook. This creates a workbook in Excel but does not save it to disk until you call SaveAs or save it through Excel.

How do I name a new Excel workbook in VBA?
Save it with newWb.SaveAs Filename:=fullPath, FileFormat:=xlOpenXMLWorkbook, where fullPath contains the destination folder and filename. Use a compatible extension and format.

Can I set the workbook name without saving it?
Not as a durable filename. An unsaved workbook has a temporary Excel name; use SaveAs to give the file a name and location. Renaming a worksheet tab is a separate action.

How do I create a workbook with macros?
Save the new workbook as .xlsm using xlOpenXMLWorkbookMacroEnabled, and ensure the VBA code is in that workbook’s VBA project. Merely creating it from another macro does not copy the source macro into the new file.

How do I stop VBA from overwriting an existing workbook?
Check whether the full destination path already exists before saving, as in the duplicate-check example. If it exists, stop, ask the user, or generate a different filename rather than automatically confirming replacement.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.