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.


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.

excel vba create new workbook and save


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.

excel vba create new workbook and save

  • 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 vba create new workbook and save

  • 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.

excel vba create new workbook and save

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

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo