Create new workbook using VBA
Creating new workbooks is done using the
Workbooks.Add method which optionally is called with a
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
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'.
Create new workbook containing only one worksheet or chart
The code below shows hot to create workbook with just one worksheet.
Dim wb As Workbook Set wb = Workbooks.Add(xlWBATWorksheet)
If you want additional worksheets, you can simply add them:
To do the same to get a workbook with one chart sheet replace
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.