How to Create New Excel Workbook using VBA

Creating a new Workbook in VBA

For creating a new Excel Workbook, you may use the Workbooks object’s Add method in VBA.

The simple way for creating the workbook is:

Workbooks.Add

It will create a new workbook with the name “BookX”. Where X is the next available number. If there is no Workbook with the name of Book1 then the newly created workbook name will be Book1 then Book2 and so on.

The examples below show how to specify the Workbook name, title, number of sheets in the new workbook, etc.

Create a simple workbook

The following VBA code creates a new workbook:

Sub worksheet_add()

 Workbooks.Add

End Sub

Output:

After executing the above code, if we execute the program again it creates another workbook with the name “Book2”.

An example of saving a Workbook with a specified name

The above code only creates a new workbook. It does not save it.

In order to save a work, you may use the SaveAs method.

In the SaveAs method, you may specify the custom name for the newly created workbook.

The VBA code below creates a new workbook with the name “Demo_book” as follows:

Code:

Sub worksheet_add()

 Workbooks.Add

 ActiveWorkbook.SaveAs "Demo_book.xlsx"


End Sub

Note that, SaveAs is the ActiveWorkbook object’s method.

By default, it saves the workbook at the same location where your VBA code file is placed.

Using Workbooks.Add properties for setting name, title, and subject of the new workbook

In the VBA program below:

• An object variable is assigned the object returned by Worksbooks.Add
• The file name of the new workbook is set by using SaveAs property of that object variable.
• Similarly, Title and Subject properties are used

Code:

Sub worksheet_add()

Set NewBook_demo = Workbooks.Add

 With NewBook_demo

 .Title = "Products Information"

 .Subject = "Products"

 .SaveAs Filename:="products_book.xlsx"

 End With


End Sub

Number of sheets in the new workbook example

By default, the workbook created by Add method contains only one sheet namely “Sheet1”, as shown in the first example above.

By using WorkSheets.Add method, you may add more sheets in the newly created workbook. See the sample code below:

Sub worksheet_add()

 Workbooks.Add

 ActiveWorkbook.SaveAs "Demo_book.xlsx"

 ActiveWorkbook.Worksheets.Add Count:=5


End Sub

Output:

As we specified 5, the new workbook contains a total of six worksheets.

Save the new workbook in the specified location

In order to save the newly created workbook in a specified/desired location rather than the same as your VBA code file, see the program below:

Code:

Sub worksheet_add()


Workbooks.Add

ActiveWorkbook.SaveAs "E:\Excel-test\book-demo.xlsx"


End Sub