Excel VBA: Create a New Workbook with Workbooks.Add

To create a new workbook in Excel VBA, use Set wbNew = Workbooks.Add. This opens a workbook in the current Excel instance; you can then fill its sheets and, if needed, save it with SaveAs.

What Workbooks.Add does

Workbooks is the collection of workbooks open in the current Excel application, and its Add method creates and opens another workbook. The new workbook becomes the active workbook, but it is safer to store it in a variable and refer to that variable explicitly. That way, later code continues to target the intended workbook even if another workbook becomes active.

With no argument, Workbooks.Add creates a workbook using Excel’s current default settings. The number and type of sheets can depend on the user’s Excel configuration. In Microsoft 365 for Windows, the setting for how many sheets a new workbook includes is under File > Options > General > When creating new workbooks. A macro should not assume that a default workbook always has a particular number of sheets.

To request a new workbook with exactly one worksheet, pass the built-in xlWBATWorksheet constant. This is useful when a macro expects a single output sheet and should not inherit the user’s default sheet count.

Create and populate a workbook with a macro

This complete example creates a one-sheet workbook, writes a small heading and date, and saves it as an ordinary Excel workbook. Before running any macro, save a copy of your source workbook: VBA actions cannot be undone with Ctrl+Z.

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

    ' Change this to a folder that exists and that you can write to.
    savePath = "C:\Reports\MonthlyReport.xlsx"

    ' Create a workbook containing one worksheet.
    Set wbNew = Workbooks.Add(xlWBATWorksheet)

    ' Write to the new workbook by referring to its variable.
    With wbNew.Worksheets(1)
        .Range("A1").Value = "Monthly report"
        .Range("A2").Value = "Created " & Format(Date, "yyyy-mm-dd")
        .Columns("A").AutoFit
    End With

    ' Save as .xlsx, a workbook format that does not store VBA code.
    wbNew.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook
End Sub

Replace the example path with a real location on your computer. The folder must already exist, and Excel needs permission to write there. If the file already exists, Excel may ask whether to replace it. Test the macro with a new filename first so an existing report is not accidentally overwritten.

Run the macro in desktop Excel

VBA macros run in the desktop versions of Excel, not in Excel for the web. On Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the macro into the module, and return to Excel. You can run it from the editor with the cursor inside the procedure by pressing F5, or return to the workbook and use Developer > Macros to select and run it.

If the Developer tab is hidden, turn it on through File > Options > Customize Ribbon and select Developer. On a Mac, use Tools > Macro > Visual Basic Editor or the Visual Basic Editor command on the Developer tab, depending on the ribbon setup. The common editor shortcut is Option+F11; some Mac keyboards require Fn as well. Excel for Mac supports VBA, but the Windows-only Alt+F11 shortcut and Windows-style file paths do not apply. Use a Mac path, such as /Users/name/Documents/MonthlyReport.xlsx, in code run on macOS.

Macros may be blocked by security settings, especially when a file came from the internet or email. Only enable macros in workbooks from a source you trust. Saving a macro in a workbook also requires a macro-enabled format such as .xlsm; a regular .xlsx file does not preserve VBA modules.

Choose the right way to add a workbook

The basic method is best when you want a clean workbook and plan to build its contents through code. A template or copied sheet can be a better starting point when the new file needs consistent formatting, formulas, print settings, or a standard layout.

Use Workbooks.Add with no argument for a quick blank workbook that follows the user’s Excel defaults.
Use Workbooks.Add(xlWBATWorksheet) when the macro needs one worksheet and should not depend on the default sheet count.
Use a template when the workbook should inherit a prepared design, such as a report layout. The template must be accessible at the path supplied to VBA.
Copy a worksheet when you need to reproduce a particular sheet’s formatting or formulas. Copying a sheet is different from creating a blank workbook, and its references may still point to the original file.

For a template, pass its filename to the Add method, for example by using Workbooks.Add("C:\Templates\Report.xltx"). This creates a workbook based on the template rather than opening the template itself for ordinary editing. Confirm the extension and path: an Excel template can be an .xltx file, or an .xltm file when the template contains macros.

Save the new workbook in the right format

Creating a workbook and saving it are separate actions. Workbooks.Add creates an open workbook, but it does not choose a permanent filename or folder. Use the new workbook object’s SaveAs method to give it a path and format. For standard workbooks, xlOpenXMLWorkbook saves as .xlsx. If the new file itself must contain VBA code, use a macro-enabled format such as xlOpenXMLWorkbookMacroEnabled and a matching .xlsm extension.

A common misconception is that saving the output as .xlsm automatically copies the macro that created it. It does not. The code remains in the workbook, add-in, or Personal Macro Workbook from which the macro is running unless your procedure explicitly transfers VBA components. In ordinary automation, the generated report often contains data and formatting only, so .xlsx is appropriate even when a separate macro created it.

For repeatable output, consider building the full path from a known folder and checking that the destination is available before saving. A fixed path is simple, but it can fail when a user does not have that folder, is working on another computer, or has a file with the same name already open. Avoid silently suppressing Excel alerts around SaveAs unless your code deliberately handles replacement behavior.

Refer to the new workbook safely

After Workbooks.Add, Excel usually makes the new file active. Code based on ActiveWorkbook can therefore work in a short macro, but it is fragile: another workbook can become active because of a user action, an event, or additional code. Saving the return value in wbNew and using expressions such as wbNew.Worksheets(1).Range("A1") makes the target explicit.

Also distinguish the workbook that contains the macro from the workbook the macro creates. If the procedure is stored in a source workbook and uses unqualified references such as Worksheets(1), those references may resolve against the active workbook, which could be the newly created file. Use ThisWorkbook when you specifically mean the workbook containing the running code, and use the variable such as wbNew when you mean the output file.

When adding more sheets, refer to the workbook object as well. For example, wbNew.Worksheets.Add adds a worksheet to that workbook. To name it, set its Name property, ensuring the name is unique and does not contain prohibited characters such as a colon or slash. This is more reliable than repeatedly selecting tabs and relying on whichever sheet happens to be active.

Common problems and how to resolve them

The macro creates more sheets than expected: the no-argument method uses Excel’s configured default. Request xlWBATWorksheet if you need exactly one worksheet.
SaveAs reports a path or permission error: verify the folder exists, the path is valid for the operating system, and the account can write there. A Windows path such as C:\Reports\file.xlsx is not a Mac path.
The output file cannot keep macros: save a workbook that contains VBA as .xlsm, not .xlsx. Remember that changing the format alone does not transfer code from the source workbook.
Code fills the wrong workbook: replace unqualified references such as Range("A1") with a fully qualified reference like wbNew.Worksheets(1).Range("A1").
The template cannot be found: check the full filename, extension, and location. A template stored on a network drive may be unavailable when disconnected.
The macro does not run: confirm that you are using desktop Excel, that the code is in a standard module or otherwise correctly placed, and that macro security permits the trusted file to run.

Debugging is easier if the macro creates the workbook first and then performs one operation at a time. If an error appears, note the highlighted line and inspect whether the workbook variable has been set, the sheet exists, and the destination path is valid. Avoid adding broad error suppression such as On Error Resume Next to hide problems; it can allow a macro to continue after an important step failed.

Safety and platform considerations

A macro can create, alter, save, or close files, so treat it as an action with side effects. Keep a backup of the source workbook before running code, and use a test output name or folder while developing. Ctrl+Z does not reverse changes made by VBA. If the macro overwrites a file, the undo stack is not a substitute for a backup.

Excel for the web can open and edit many workbooks, but it cannot run VBA macros or create a new workbook by executing this code in the browser. Open the file in desktop Excel to run the procedure. On Mac, VBA workbook creation works, but platform-specific automation, paths, and some object-model details can differ from Windows. The example uses core workbook and worksheet operations rather than Windows-specific automation.

Frequently Asked Questions

How do I create a blank workbook in Excel VBA?
Declare a Workbook variable and assign the result of Workbooks.Add to it. Use Workbooks.Add(xlWBATWorksheet) if you want the new workbook to contain one worksheet regardless of the user’s default sheet setting.

Does Workbooks.Add save the new file automatically?
No. It creates and opens a workbook, but the workbook is not saved to a permanent location by that call. Use wbNew.SaveAs with a filename and an appropriate file format to save it.

How can I create a workbook with one sheet only?
Pass the built-in constant xlWBATWorksheet to Workbooks.Add. This requests one worksheet rather than relying on the configurable default number of sheets.

Can I create a workbook from an Excel template?
Yes. Supply the accessible template filename to Workbooks.Add. The new workbook is based on that template; check the path and use the correct template extension, such as .xltx or .xltm.

Can Excel for the web run a VBA macro that creates a workbook?
No. VBA macros run in desktop Excel, including supported Windows and Mac versions. Open the workbook in the desktop app to execute the macro.

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.