VBA Create New Workbook: Code, Options, and Tips in Excel

To create a new Excel workbook with VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates a workbook using Excel’s default settings; you can also specify a blank workbook with one worksheet or start from a template. The new workbook is initially unsaved, so use SaveAs if you want to give it a file name and location.

What Workbooks.Add Does

Workbooks is the collection of open workbooks in the current Excel application, and its Add method creates another workbook and returns a Workbook object. Assigning that object to a variable lets your macro refer to the new file directly, rather than relying on whichever workbook happens to be active.

With no argument, Workbooks.Add follows Excel’s default workbook settings. In Microsoft 365 for Windows, the number of worksheets in a new blank workbook can depend on the setting at File > Options > General > When creating new workbooks > Include this many sheets. A macro that assumes a particular number of sheets may therefore behave differently on another computer.

To request a new workbook with exactly one worksheet, pass the built-in constant xlWBATWorksheet. This is useful for reports that need a predictable starting structure. A workbook can also be created from a template, which can supply preformatted sheets, formulas, styles, or other prepared content.

Create a Blank Workbook in a Macro

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste the macro below. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.

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

    ' Start with exactly one worksheet.
    Set newWb = Workbooks.Add(Template:=xlWBATWorksheet)
    Set ws = newWb.Worksheets(1)

    ' Add a small example so the new workbook is easy to identify.
    ws.Name = "Summary"
    ws.Range("A1").Value = "Created by VBA"
End Sub

Run the procedure with the cursor inside it and press F5, or return to Excel and use Developer > Macros. If the Developer tab is missing on Windows, enable it through File > Options > Customize Ribbon and select Developer. On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well. The Developer tab can be enabled from Excel > Settings > Ribbon & Toolbar.

The code creates a workbook and puts it in Excel’s open-workbook collection. It does not close the workbook that contained the macro, and it does not save the new file automatically. Keeping a reference in newWb is safer than assuming the active workbook is always the one your code intends to edit.

Save the New Workbook

Creating a workbook and saving it are separate operations. The following example creates a one-sheet workbook and saves it as an ordinary Excel workbook. Change the path to a folder that already exists and that you are allowed to write to.

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

    ' Create a workbook with one worksheet.
    Set newWb = Workbooks.Add(Template:=xlWBATWorksheet)
    savePath = "C:\Reports\NewReport.xlsx"

    ' The Reports folder must exist before this line runs.
    newWb.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook
End Sub

The xlOpenXMLWorkbook format corresponds to the standard .xlsx format. If a file with the same name already exists, Excel may display an overwrite confirmation. Do not turn off Excel alerts casually to suppress that prompt: doing so can cause a macro to replace a file without the expected warning. If files must never be overwritten, add a check for the destination file before calling SaveAs.

A path such as C:\Reports\NewReport.xlsx is a Windows example, not a guaranteed folder on every computer. The directory must be present, and a network or cloud location may require access or synchronization. On a Mac, use an appropriate Mac file path. If the macro should let a person choose a destination, use Excel’s file dialog rather than assuming a fixed folder.

Choose Between a Blank Workbook and a Template

A blank workbook is appropriate when the macro will build the structure itself. A template is better when each new file should start with the same layout, print settings, styles, formulas, or instructions. The template file should be available at the path supplied to VBA.

For a template, replace the Workbooks.Add line with the template form shown in this complete example:

Sub CreateFromTemplate()
    Dim newWb As Workbook
    Dim templatePath As String

    ' Set this to the location of an existing Excel template.
    templatePath = "C:\Templates\MonthlyReport.xltx"
    Set newWb = Workbooks.Add(Template:=templatePath)
End Sub

Excel also accepts constants for a single worksheet or a chart sheet, including xlWBATWorksheet and xlWBATChart. Use a chart-only workbook only when that is genuinely the desired starting point. A common source of confusion is expecting Workbooks.Add to copy the current workbook: it creates a new workbook, but it does not duplicate the source workbook’s sheets or data.

Copy an Existing Worksheet Into a New Workbook

If your goal is to export a particular sheet rather than create an empty file, use the worksheet’s Copy method. When neither a Before nor an After destination is supplied, Excel creates a new workbook containing the copied worksheet. The new workbook becomes active, so capture it after the copy:

Sub CopySheetToNewWorkbook()
    Dim newWb As Workbook

    ' Copying without a Before or After argument creates a new workbook.
    ThisWorkbook.Worksheets("Data").Copy
    Set newWb = ActiveWorkbook
End Sub

ThisWorkbook means the workbook containing the running VBA code; it is not necessarily the same as ActiveWorkbook, which means the workbook currently in front. A copied sheet may bring its formatting, formulas, and other sheet-level content, but references to other sheets or workbooks can need attention. Check formulas, named ranges, links, and any charts or controls after exporting.

For a full copy of an existing workbook, use a workbook-level copy or save operation appropriate to the intended result rather than copying one worksheet. The distinction matters: copying one tab does not automatically reproduce every tab and workbook-level setting from the original.

Save in the Right File Format

Choose a format that matches what the new workbook contains and how it will be used. The extension and the FileFormat argument should agree. For example, saving a macro-containing workbook as .xlsx does not preserve its VBA project; use a macro-enabled format when the file itself must retain VBA.

xlOpenXMLWorkbook saves a standard .xlsx workbook without VBA macros.
xlOpenXMLWorkbookMacroEnabled is used for an .xlsm workbook that needs to retain VBA.
xlExcel12 corresponds to the Excel binary workbook format, .xlsb.

When the macro is stored in one workbook and creates a separate .xlsx output, that output can be macro-free even though the source file contains code. Conversely, if you intend to put VBA into the new workbook, save it as .xlsm and ensure the code is actually stored in that workbook. Excel for the web can open and edit many workbook files, but it cannot create or run VBA macros; use the desktop Excel application to run this code.

Common Problems and Safer Practices

Most failures come from assumptions about workbook state, paths, or security rather than from the Add method itself. Check these points when a macro does not produce the expected file:

The new workbook is not saved. Workbooks.Add opens a workbook but does not assign a permanent file path. Call SaveAs when you need a saved output.
The path is invalid. Confirm the folder exists, the file name is valid, and your account can write there. A folder used on one computer may not exist on another.
The wrong workbook is being edited. Store the returned workbook in an object variable and qualify references with that variable, such as newWb.Worksheets(1).
The sheet count varies. Specify xlWBATWorksheet when the macro requires one worksheet instead of relying on the user’s default workbook setting.
Excel blocks the macro. Macro security settings, downloaded-file warnings, or organizational policies can prevent VBA from running. Only enable macros in files you trust.
An existing file is replaced or a prompt appears. Decide deliberately how the macro should handle an occupied destination before saving. Avoid suppressing alerts unless the code safely handles collisions.

On Windows, save a backup before running a macro that edits or creates files. Ctrl+Z does not reverse VBA operations. Excel for Mac supports VBA, but some Windows-specific features, paths, and automation interfaces may differ; a macro that relies on Windows APIs may need changes to work on Mac.

Frequently Asked Questions

What is the VBA code to create a new workbook?

Use Workbooks.Add. To make the starting sheet count predictable, use Set newWb = Workbooks.Add(Template:=xlWBATWorksheet) after declaring newWb As Workbook. The result is open but unsaved.

How do I create a workbook with one sheet in VBA?

Pass xlWBATWorksheet to Workbooks.Add. This asks Excel for a new workbook with one worksheet instead of relying on the default number configured in Excel’s options.

Does Workbooks.Add save the workbook automatically?

No. It creates and opens the workbook, but it does not give the file a permanent name or location. Use the new workbook’s SaveAs method and specify a valid path and file format.

How do I create a workbook from an Excel template?

Use Workbooks.Add(Template:=templatePath), where templatePath contains the full path to an existing Excel template. The template must be reachable when the macro runs.

Can VBA create a new workbook in Excel for the web?

Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run a VBA procedure; the resulting workbook can then be saved and used where its format is supported.

Which Method Should You Use?

Use Workbooks.Add for a genuinely new workbook, choose xlWBATWorksheet when you need exactly one initial sheet, and use a template when each output should follow a prepared design. Use worksheet Copy when the output should contain an existing tab. In every case, hold a reference to the workbook object, decide whether and where to save it, and confirm the chosen file format before distributing the result.

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.