Create New Workbook and Save Using VBA in Excel

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.

How to Create New Workbook and Save Using VBA in Excel: 2 Easy Ways

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.

Steps:

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.

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)”

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

Download Practice Workbook

Download this workbook and practice while going through the article

Create and Save New Workbook.xlsm

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.