Excel VBA: Open a New Workbook with Workbooks.Add

To open a new workbook in Excel VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates and activates a workbook, while storing its reference in newWb lets your macro work with it reliably.

What Workbooks.Add Does

Workbooks.Add creates a new workbook in the current Excel application. By default, Excel uses the user’s workbook settings to decide how many sheets to include. The new workbook becomes the active workbook, but a macro should not rely on that active status to identify its target: another workbook or window could become active while the code runs.

Assign the returned workbook to an object variable, then qualify operations with that variable. This keeps your code clear about which workbook it is editing. For example, newWb.Worksheets(1) refers to the first worksheet in the workbook you just created, not necessarily the first sheet in the workbook containing the macro.

The method can create a blank workbook, a workbook with a specified sheet type, or a workbook based on a template. It does not automatically save the result to disk. A newly created workbook exists in Excel’s memory until you save it, close it, or Excel closes.

Create a Blank Workbook in VBA

In Excel for Microsoft 365 on Windows, the basic pattern is to declare a Workbook variable and assign the result of Workbooks.Add to it. This example creates a workbook with exactly one worksheet, gives the sheet a name, and writes a heading:

Sub CreateNewWorkbook()
    Dim newWb As Workbook
    Dim ws As Worksheet

    ' Save a copy of important work before running a macro.
    Set newWb = Workbooks.Add(Template:=xlWBATWorksheet)
    Set ws = newWb.Worksheets(1)

    ws.Name = "Summary"
    ws.Range("A1").Value = "Monthly report"
End Sub

The xlWBATWorksheet argument asks Excel to create a workbook with one worksheet. If you omit the argument, Excel uses its configured number of sheets for new workbooks, so a default workbook may contain more than one. You can also use xlWBATChart when you specifically want a new workbook containing a chart sheet rather than a worksheet.

To enter this code, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Return to Excel and run the macro from Developer > Macros, or press Alt+F8 and choose its name. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Mac users can open the Visual Basic Editor with Option+F11 or Fn+Option+F11, depending on their keyboard settings; the Developer tab and macro options are also available in Excel for Mac.

Choose the Right Way to Create It

Use the simplest method that fits the result you need. The main choices control the sheet layout or start from a file that already contains formatting and structure:

Default blank workbook: Workbooks.Add creates a workbook using Excel’s new-workbook sheet settings. This is convenient, but the number of sheets can vary between computers.
One worksheet: Workbooks.Add(Template:=xlWBATWorksheet) makes the result predictable when the macro expects one worksheet.
Chart-only workbook: Workbooks.Add(Template:=xlWBATChart) creates a workbook with a chart sheet. Use this only when a chart sheet, rather than a normal grid worksheet, is intended.
Workbook from a template: pass the full path of an Excel template file as the Template argument. This is useful when the new file should inherit prepared styles, formulas, headers, or page settings.

A template is different from copying an existing workbook. A template is designed to provide a starting structure for new files; copying an existing workbook may bring along data, links, hidden sheets, or other content you did not intend to reuse. Check the template’s contents before using it in an automated process.

Work with the New Workbook Safely

VBA’s ActiveWorkbook refers to whichever workbook is active at a particular moment. By contrast, ThisWorkbook means the workbook that contains the running VBA code. If your macro is stored in a macro-enabled workbook and creates another workbook, those references point to different files. This distinction is a frequent source of mistakes.

For example, use newWb.Worksheets(1).Range("A1") to target a cell in the new workbook. Avoid writing ActiveSheet.Range("A1") unless the active sheet is intentionally the target. Explicit references make the macro easier to maintain and reduce the chance of placing data in the code workbook by accident.

You can also add sheets after creating the workbook. If you need an additional worksheet, use newWb.Worksheets.Add and keep the returned worksheet in a variable. When referencing sheets by name, verify that the name exists and is unique; Excel does not allow duplicate worksheet names within the same workbook.

Macros cannot be undone with Ctrl+Z, and Excel’s ordinary Undo history is generally cleared when VBA changes the workbook. Save a copy of important work before running a macro, particularly if the macro edits source data as well as creating a separate file.

Save the New Workbook and Pick a Format

Creating a workbook and saving it are separate actions. Call the new workbook’s SaveAs method when you want to give it a path and file format. This example saves a workbook as a standard macro-free Excel file:

Sub CreateAndSaveWorkbook()
    Dim newWb As Workbook
    Dim outputPath As String

    ' Save a copy of important work before running a macro.
    outputPath = "C:\Reports\MonthlyReport.xlsx"
    Set newWb = Workbooks.Add(Template:=xlWBATWorksheet)

    newWb.Worksheets(1).Range("A1").Value = "Monthly report"
    newWb.SaveAs Filename:=outputPath, _
        FileFormat:=xlOpenXMLWorkbook
End Sub

The folder in outputPath must already exist, or the save will fail. Change the example path to a real folder on your computer. The xlOpenXMLWorkbook format corresponds to .xlsx, which does not store VBA code. If the new workbook itself needs to retain macros, use the .xlsm extension and xlOpenXMLWorkbookMacroEnabled as the file format.

Be deliberate about overwrite behavior. If a file with the same name already exists, Excel may display a replacement prompt, depending on application settings and how the macro is run. A production macro should check whether the destination exists and decide whether to stop, choose another name, or intentionally replace it. Do not suppress alerts without planning what should happen to an existing file.

Use a Template or Existing Workbook

To base a new workbook on a template, provide its full path to Workbooks.Add. For instance, a template such as C:\Templates\Report.xltx can supply a standard report layout. In VBA, the template path must be a valid string and the file must be accessible to the person running the macro. If the path points to a network location, connectivity and permissions matter too.

Templates are useful for repeatable documents because they keep layout decisions out of the macro. The template can define column widths, number formats, print areas, and introductory text. The macro can then fill in changing values. However, formulas or external links embedded in a template may also be carried into new files, so inspect them as part of maintaining the automation.

If your aim is to open an existing workbook rather than create a new one, use Workbooks.Open, not Workbooks.Add. Workbooks.Open loads a file already stored at a path; Workbooks.Add creates a new workbook or starts one from a template. The names are similar, but they solve different tasks.

Common Problems and How to Fix Them

The macro edits the wrong workbook: qualify references with the workbook variable returned by Workbooks.Add. Do not assume that ActiveWorkbook or ActiveSheet will remain the intended target throughout a longer procedure.
The new workbook has an unexpected number of sheets: specify xlWBATWorksheet for a predictable one-worksheet workbook, or add and remove sheets explicitly.
The save fails: check that the folder exists, the filename is valid, the destination is writable, and the file is not blocked by permissions or another process.
The file saves without its macros: use the macro-enabled .xlsm extension and matching file format if VBA must be stored in that workbook. A standard .xlsx file does not retain VBA projects.
Excel shows a security warning or blocks the macro: macros may be disabled by Trust Center policy or organizational settings. Only run code from a source you trust, and do not lower security settings simply to make an unfamiliar macro run.

If a macro creates a workbook and then stops with an error, inspect which line failed and whether the workbook was created before the failure. Excel may leave the new, unsaved workbook open. Close it only after deciding whether it contains work you need to keep.

Windows and Mac Considerations

The core VBA method Workbooks.Add is available in desktop Excel for both Windows and Mac. The VBA editor, macro settings, and keyboard shortcuts differ somewhat by platform, and the example save path is Windows-specific. On Mac, replace it with a valid macOS path, such as a path in the user’s Documents folder, and ensure the macro has access to that location.

Excel for the web can open and edit many workbooks, but it does not run VBA macros. To execute this code, use the desktop Excel application. A workbook with VBA can be stored in a cloud location, but the macro still needs to be run in desktop Excel, subject to the organization’s security policies.

Frequently Asked Questions

What is the VBA code to open a new Excel workbook?
Use Workbooks.Add. To retain a clear reference to the result, declare a variable as Workbook and assign it with Set newWb = Workbooks.Add.

How do I create a workbook with only one sheet?
Use Set newWb = Workbooks.Add(Template:=xlWBATWorksheet). This requests a new workbook with one worksheet, rather than relying on the user’s default new-workbook sheet setting.

What is the difference between Workbooks.Add and Workbooks.Open?
Workbooks.Add creates a new workbook, optionally from a template. Workbooks.Open opens an existing file from a path.

Does Workbooks.Add save the workbook automatically?
No. The workbook is created in Excel but is not saved to a file until you call SaveAs or save it through Excel. A macro should specify a valid destination and file format when saving automatically.

Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro.

Why did the new workbook lose its macro?
It may have been saved as .xlsx, a format that does not retain VBA code. Save a workbook that must contain macros as .xlsm using the macro-enabled file format.

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.