VBA Create New Workbook (Excel File)

Use Workbooks.Add. It creates a new, unsaved workbook and makes it the active one. Store it in a variable so the rest of the code points at the right file:

Sub CreateWorkbook()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    wb.Worksheets(1).Range("A1").Value = "Report"
    wb.SaveAs Filename:="C:\Reports\Sales.xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

The new file gets the number of sheets set in File → Options → General → Include this many sheets. For a single sheet of a set type, pass a constant to the Template argument: Workbooks.Add xlWBATWorksheet for one worksheet, or Workbooks.Add xlWBATChart for one chart sheet.

To start from an existing file’s layout, pass its path as the template: Workbooks.Add Template:="C:\Templates\Invoice.xlsx". The new workbook copies that file’s sheets, formatting and formulas, and the original stays unchanged.

Match the extension to FileFormat when saving. Use .xlsx with xlOpenXMLWorkbook, and .xlsm with xlOpenXMLWorkbookMacroEnabled. A mismatch, such as a .xlsm name with no file format given, stops the macro with error 1004.

If a file with that name already exists, Excel asks whether to replace it, and clicking No stops the macro with error 1004. Check first with If Dir("C:\Reports\Sales.xlsx") <> "" Then, or set Application.DisplayAlerts = False before saving to overwrite without the prompt.

Table of Content hide

Create a New Workbook using VBA

Add a New Workbook using a Template

Create a New Excel Workbook and Save it

Create a New Workbook using VBA

Make sure to add the developer tab to the ribbon to enter this code into the VBE.

Type the keyword “Workbooks” to refer to the workbook object.
After that, type a dot.
Here you’ll have a list of properties and methods to select.
Select “Add” from that list or type it.

Sub vba_new_workbook()
Workbooks.Add
End Sub

Add a New Workbook using a Template

As I said, we are using the Workbooks.Add method. With this method, there’s an argument (optional) that you can use to refer to a file as a template.

Workbook.Add Template (Optional)

Let’s say you have a workbook and want to have the new workbook exactly the same as it, you can refer to it as a template.

Workbooks.Add Template:="C:UsersDellDesktopbook1.xlsx"

When you run the above code, it takes the reference from the “book1” which is saved on the desktop. The template workbook has 6 worksheets and the new workbook has exactly the same number of worksheets.

Apart from this, you can use the default arguments to decide what type of sheet you want to have in the new workbook.

xlWBATChart: Chart Sheet
xlWBATExcel4IntlMacroSheet: Macro Sheet Version 4
xlWBATExcel4MacroSheet: Macro Sheet (International) Version 4
xlWBATWorksheet: Worksheet

Create a New Excel Workbook and Save it

When you create a new workbook, Excel opens it but will not save it with the Add method. So for this, you need to use the SaveAs method.

Sub vba_create_workbook()
Workbooks.Add
ActiveWorkbook.SaveAs "C:usersdelldesktopmyBook.xlsx"
End Sub

First, use the workbook.add to create a new workbook.
Next, refer to the active workbook and use the SaveAs method.
In the SaveAs method, use the path where you want to save the workbook along with the name of the file.
In the end, run the code.

What is VBA

Create a New Workbook using VBA

Add a New Workbook using a Template

Create a New Excel Workbook and Save it

Related Tutorials