Create new workbook using VBA

Creating new workbooks is done using the Workbooks.Add method which optionally is called with a Template argument which can stand for different things as dsiscussed below. The method returns a Workbook object. The new workbook becomes the active workbook.

Create new workbook with no template specified

Creating a new workbook can be as simple as the code below:


Dim wb As Workbook
Set wb = Workbooks.Add

The new workbook's name (wb.Name) will be 'Book(x)' where (x) will be a sequential number (1,2,...). The new workbook contains a number of blank sheets, the number of sheets is determined by the Application.SheetsInNewWorkbook property.

Create a new workbook from a template file

The new workbook can also be created with a specified file as a template:


Dim strTemplate As String: strTemplate = "c:\temp\myfile.xlsm"
Dim wb As Workbook
Set wb = Workbooks.Add(strTemplate)

The new workbook is a full copy of the file which served as template - including VBA code if any. The new workbook however does not yet exist in the file system.

The new workbook's name will be after the template with a sequential number added, e.g. here 'myfile1'.

Note
  • Though the argument name here is Template, any file that can be opened in Excel is allowed - it need not be an Excel template type (.xltx or similar)
  • An alternative way to create a new file is using Workbooks.Open and specifying a real Excel template type file in the Filename argument and Editable:=False. This allows for some extra arguments such as UpdateLinks.

Create new workbook containing only one worksheet or chart

The code below shows how to create workbook with just one worksheet. It uses XlWBATemplate which specifies the type of workbook to create. 


Dim wb As Workbook
Set wb = Workbooks.Add(xlWBATWorksheet)

If you want additional worksheets, you can simply add them:


wb.Worksheets.Add Count=4

To do the same to get a workbook with one chart sheet replace xlWBATWorksheet by xlWBATChart.

Below image shows how you can use Code VBA to add the required fragment of code to your program. Note that the hover over the menu item shows the method code that will be inserted.

create new workbook menu