How to Create a New Workbook in VBA Macro

In this blog post, you will learn to create a new workbook in VBA.

In Excel, simply go to “File” and click “New” and then select the blank file from the list.

Here in VBA, we have two ways to create a new workbook or file by VBA macro.

Create Workbook without File Name

Whenever we create a new workbook in Excel, it opened with the name “Book.xlsx” by default. Then we can save the file with the desired name.

Below the macro will create a new workbook in Excel with the name “Book1.xlsx”.

If “Book1.xlsx” is already opened or existing there then macro will create a workbook name with “Book2.xlsx” by default.

For creating a macro for a new workbook, just follow the below procedure.

Go to the Developer tab in Excel and click on Visual Basic and then go to Insert and open a new module.

Just type the below code in the module and then press “F8” multiple times until End Sub for step by step execution.

Sub New_workbook() Workbooks.Add End Sub

After running the above code in VBA, see the result as the below image.

\

Image-01

We cannot locate this file until we save it with a file name in our folder or storage drive.

Create Workbook with File Name

Now we will learn how to create a new workbook with the desired name by VBA macro.

Just copy the below VBA macro in the new module, run the code by pressing “F8” again and again for step by step execution until End Sub, or just press “F5” from the keyboard to run in a single time.

Sub New_Workbook() Workbooks.Add.SaveAs Filename:="E:\Excelnsg.xlsx" End Sub

After executing the above code, see the below image for the result.

File “Excelnsg” is created now in “E” storage drive.

Image - 02

So here we learn about to create a new workbook by VBA macros.

I hope you find this blog post useful.

Please feel free to put your comment or suggestion in the comment box below.

Thanks

Narendra

Related Post

Easiest Way to Copy and Paste Data in Excel VBA

How to Move Sheets in Excel VBA

What is Current Region Property in VBA

How to Change or Rename the Sheet Name in Excel VBA

How to Change Font Color in Excel VBA

How to Change Sheet Tab Color in Excel VBA

How to Change the Background Color in Excel VBA