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.
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.
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.
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.
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.
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.
Sub CreateNewWb () Workbooks.Add MsgBox ActiveWorkbook.Name Workbooks.Add.SaveAs Filename:="CreateNewWB" Workbooks("CreateNewWB ").Activate ActiveWorkbook.Worksheets.Add Count:= 5 End Sub
A new workbook is created and saved in the given name. It is reactivated and 5 sheets are added to the workbook.