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:
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
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:
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
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
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:
Sub worksheet_add() Workbooks.Add ActiveWorkbook.SaveAs "E:\Excel-test\book-demo.xlsx" End Sub