If you want to create a new Excel workbook using VBA, you can do that using the Workbooks.Add method.
In this article, I will give you some simple macro code examples to create a new workbook using VBA.
Create a New Workbook
If you want to just create a new workbook (without saving it), you can use the code below:
Sub CreateNewWorkbook()
' Creates a new workbook
Workbooks.Add
End SubThe above code will create a new workbook, and that new workbook will be activated (which means your cursor will be taken to the active cell in the newly created workbook).
Note that this code only creates a new workbook and doesn’t save it.
Create a New Workbook and Save It
Sub CreateandSaveNewWorkbook()
' Declare variable to hold the reference to the new workbook
Dim newWb As Workbook
' Create a new workbook and store its reference to newWb variable
Set newWb = Workbooks.Add
' Saves the new workbook with a specific name in specified location
newWb.SaveAs "C:\Users\steve\Downloads\NewWorkbook.xlsx"
End SubThe above code first creates the workbook and then saves it using the name NewWorkbook.xlsx in the specified location. You can change the name and the folder location in the code above.
Note that the newly created file will remain open after running this code.
If you want the code to create a new workbook, save it, and then close it, you can use the code below:
Sub CreateandSaveNewWorkbook()
' Declare variable to hold the reference to the new workbook
Dim newWb As Workbook
' Create a new workbook and store its reference to newWb variable
Set newWb = Workbooks.Add
' Saves the new workbook with a specific name in specified location
newWb.SaveAs "C:\Users\steve\Downloads\NewWorkbook.xlsx"
' Close the workbook
newWb.Close
End Sub
Note that if the folder doesn’t exist or there is an error in the name of the folder path, you will see an error message box as shown below:
Also read: How to Open Excel Files Using VBA
Create a New Workbook with a Specific Number of Sheets
When you create a new workbook, it will have the default number of sheets (1 in new versions and 3 in older versions). If you want the new workbook to have more sheets, you can use the code below (where it creates 10 sheets in the new workbook).
Sub CreateNewWorkbookWith10Sheets()
Dim i As Integer
Dim newWorkbook As Workbook
Dim existingSheetCount As Integer
' Create a new workbook
Set newWorkbook = Workbooks.Add
' Count existing sheets in the new workbook
existingSheetCount = newWorkbook.Sheets.Count
' Calculate and add the number of sheets needed to make it 10
For i = 1 To 10 - existingSheetCount
Sheets.Add After:=newWorkbook.Sheets(newWorkbook.Sheets.Count)
Next i
End Sub
The above code first checks the number of existing sheets in the workbook and then adds more sheets to bring the number to 10.
Also read: VBA Close Workbook
Create a New Workbook Using a Template
Sub CreateWorkbookFromTemplate()
' Declare a variable to store the new workbook object
Dim newWb As Workbook
' Create a new workbook based on the template
Set newWb = Workbooks.Add("C:\Templates\MyTemplate.xltx")
' Save the new workbook
newWb.SaveAs "C:\NewFiles\NewWorkbook.xlsx"
End SubThis above code does the following:
- It declares a variable (newWb) to store the reference to the new workbook.
- It creates a new workbook based on a template file (“MyTemplate.xltx”) located in the “C:\Templates” directory.
- The new workbook is saved as “NewWorkbook.xlsx” in the “C:\NewFiles” directory.
This allows you to easily replicate the structure and formatting of the template file into a new workbook. You can customize this further based on your specific needs.
Also read: Useful Excel VBA Macro Codes Examples
Other articles you may also like:
