Creating New Workbooks in Excel VBA

In this post, you’ll be learning about workbooks and how to create new workbooks in Excel VBA with few simple steps, add worksheets and later save the workbook.

How to Access VBA in Microsoft Excel?

To access the VBA feature in Excel, follow the below steps.

• Under the Developer tab, click visual basic

• From the insert option, click insert module.

How to Create New Workbook in Excel VBA?

You can create workbooks in Excel VBA using the Workbooks.Add property.

Code

Sub CreateNewWb()

Workbooks.Add

End Sub

The newly added Workbook is now the Active Workbook.

To confirm this, use the below code, the MsgBox will display the active workbook name.

Code:

Sub CreateNewWb ()

Workbooks.Add

MsgBox ActiveWorkbook.Name

End Sub

The message box in this code will display the name the workbook that is active and not the newly opened one.

How to Save Workbook using Excel VBA?

To create a workbook in Excel VBA and save it, follow these steps.

Workbooks.Add.SaveAs Filename:="CreateNewWB"

This command will save the Workbook as an .xlsx file to folder which you have set as the default folder.

When you need to access the particular workbook, enter the below code in the VBA module.

Workbooks("CreateNewWB ").Activate

This code will Activate “CreateNewWB.xlsx”.

How to Create New Workbook & Add Sheets using Excel VBA?

To create New workbook and add sheets to it, follow these steps.

Run a macro with the following code.

ActiveWorkbook.Worksheets.Add Count:=3

This code will add three sheets to the workbook , as the count is equal to 3

Sample program using creating a workbook property and its related features in Excel VBA.

Code:

Sub CreateNewWb ()

Workbooks.Add

MsgBox ActiveWorkbook.Name

Workbooks.Add.SaveAs Filename:="CreateNewWB"

Workbooks("CreateNewWB ").Activate

ActiveWorkbook.Worksheets.Add Count:= 5

End Sub

Output

A new workbook is created and saved in the given name. It is reactivated and 5 sheets are added to the workbook.