Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Visual Basic Application (VBA) is the programming language used in Microsoft Excel. We can do all the stuff using VBA. In this article, I will show you the methods of using Excel VBA to create a new workbook and save the workbook.
Download Practice Workbook
Download this workbook and practice while going through the article
2 Easy Ways to Create New Workbook and Save Using VBA in Excel
This is a simple workbook in Excel. I will use this workbook and show you the methods of using Excel VBA to create a new workbook and save the workbook.
1. Applying Workbooks Object to Create New Workbook and Save
In this section, I will discuss the 1st method. This is a very simple method. Let’s proceed.
- Go to the Developer
- Then, select Visual Basic.
- After that, Visual Basic Window will appear.
- Then, go to the Insert
- Next, select the New Module.
- A new module will pop up. Write down the following code.
Sub create_and_save_workbook()
Workbooks.Add
ActiveWorkbook.SaveAs "G:\Softeko Digital\74. VBA Workbook\New Workbook.xlsx"
End Sub
VBA Code Breakdown:
- First, I have created a Sub Procedure create_and_save_workbook.
- Next, I have given Add command
- After that, I have used SaveAs feature to save the new workbook
- “G:\Softeko Digital\74. VBA Workbook” is the location where I am going to save the file.
- “New Workbook” is the file name.
- “xlsx” is the extension
- Click the icon to run the program.
- Excel will create and save a new workbook in the selected location.
Read More: Excel VBA to Add Sheet to Another Workbook (3 Handy Examples)
2. Using Variable to Create New Workbook and Save
In this section, I will show another method of using Excel VBA to create a new workbook and save the workbook. This time, I will define a new variable and create a new workbook.
Steps:
- Bring a new module following method-1.
- Then, write down the following code in the module.
Sub create_and_save_new_workbook()
Dim nwb As Workbook
Set nwb = Workbooks.Add
nwb.SaveAs "G:\Softeko Digital\74. VBA Workbook\New Workbook(2).xlsx"
End Sub
VBA Code Breakdown:
- First, I have created a Sub Procedure create_and_save_new_workbook
- Then, I have defined the nwb variable as a Workbook.
- Next, I have set the variable nwb as Add command
- After that, I have used SaveAs feature to save the new workbook
- “G:\Softeko Digital\74. VBA Workbook” is the location where I am going to save the file.
- “New Workbook(2)” is the file name.
- “xlsx” is the extension
- Click the icon to run the program (see image).
- Excel will create and save a new workbook in the selected location.
Note: Since I have saved a file named “New Workbook” in the 1st method, I must save another file with a different name. That’s why the new file’s name is “New Workbook(2)”
Read More: Macro to Create New Sheet and Copy Data in Excel (4 Examples)
Things to Remember
- You can press ALT+F11 to bring up the VBA
- You can use the keyboard shortcut F5 to run the VBA code
Conclusion
In this article, I have shown 2 simple methods to create a new workbook and save using VBA in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.
Related Articles
- Excel Macro: Create New Sheet and Rename (3 Ideal Examples)
- Add Sheet with Name from Cell Using Excel VBA
- Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)
- How to Add Sheet with Name in Excel VBA (6 Easy Ways)
- Excel VBA: Add Sheet After Last (3 Ideal Examples)
- Create New Sheet from Template Using Macro in Excel
- Excel VBA to Add Sheet If It Does Not Exist (with Quick Steps)
