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 |
---|
|
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.