Create a new workbook in excel vba

The Add method of the Workbooks collection is used to create a new workbook. The new workbook becomes the active workbook. Creating a new Workbook is done by adding a new item to the Workbookscollection.

In its simplest form it is called like this:


Workbooks.Add

The Workbooks collection object here is obtained from the Application.Workbooks procedure.

Note: Because the Application.Workbooks method is global Application may be left out from the statement.

In most cases you will follow up the creation of the new Workbook object with further actions on it. To make this possible you use the procedure call to assign the created object to an object variable, here wb .


Dim wb As Workbook
Set wb = Workbooks.Add

The optional Template argument

The optional Template argument determines how the new workbook is created. If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets, the number of sheets is determined by the SheetsInNewWorkbook property.


Application.SheetsInNewWorkbook = 2 'Setting the SheetsInNewWorkbook value
Dim lngSheetsInNewWorkbook As Long
lngSheetsInNewWorkbook = Application.SheetsInNewWorkbook 'Obtaining the current value

Warning: Changing the SheetsInNewWorkbook property using code affects the workings of creating a new workbook in general and may not be a wise thing to do.

Using a template file to create the new workbook from

If this argument is a string specifying the name of an existing Microsoft Excel file, the new workbook is created with the specified file as a template. If you locate the template in the designated template folder (here: \Documents\Custom Office Templates), you can ignore the path:


Dim wb As Workbook
Set wb = Workbooks.Add(Template:="MyTemplate.xltx")

If you intend to store the template in another folder, you have to specify the full path:


Dim wb As Workbook
Set wb = Workbooks.Add(Template:="C:\Code VBA\Demo\MyTemplate.xltx")

Workbook with single sheet of selected type

By using one of the XlWBATemplate constants as Template argument you create a new workbook with a single sheet of the specified type: xlWBATChart to get a Chart sheet or xlWBATWorksheet for a Worksheet.


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

Workbook with multiple sheets of selected type

Given the risks involved with using the SheetsInNewWorkbook property (described above), I propose to start with a single sheet and add as many sheets as you require to it, below three in total:


Dim wb As Workbook
Set wb = Workbooks.Add(Template:=xlWBATWorksheet) '1
wb.Sheets.Add(Type:=xlWorksheet, Count:=2) 'The number of sheets to be added.

Create a new workbook and name it

The name of a saved workbook is its filename, including the file extension. A workbook which has not yet been saved obtains a temporary name such as Book1 - which is not very distinctive. To act on a workbook you have to identify it within the Workbooks collection.

To give the workbook it has to be saved:


Dim wb As Workbook
Set wb = Workbooks.Add
wb.SaveAs

The above SaveAs has no parameters specified and uses only defaults. The file will be named after its temporary name Book1.xlsx and saved in the current folder.

Create new workbook vba and copy sheets

A common situation is that one wants to copy some sheets with data to a newly created workbook. To achieve this it is important to first identify the objects involved: the source workbook wbSource, the new workbook wbNew, the worksheet to be copied wsToCopy, and the location in the new workbook where you want to add it wsPasteLocation. After having done that, one simply calls the wsToCopy.Copy supplying the location where to copy the sheet to:After:=wsPasteLocation


Dim wbNew As Workbook
Set wbNew = Workbooks.Add
Dim wbSource As Workbook
Set wbSource = Workbooks.Open(Filename:="C:\Code VBA\Demo\Data.xlsx")
Dim wsToCopy As Worksheet
Set wsToCopy = wbSource.Sheets("Sheet1")
Dim wsPasteLocation As Worksheet
Set wsPasteLocation = wbNew.Sheets("Sheet1")
wsToCopy.Copy After:=wsPasteLocation