Create and Save New Workbook in VBA

Visual Basic for Applications (VBA) allows us to automate every aspect of our work in Excel. This can relate to any level that we are in, whether we talk about the application as a whole, a certain workbook, or a particular sheet. 

In the example below, we will show how can we manipulate Excel on the application level with VBA to create and save a new workbook with VBA

Create New Workbook in Vba

To access the VBA module, we will click the combination of ALT + F11 on our keyboard. On the window that appears, we will right-click on the left side, and choose Insert >> Module:

Graphical user interface, application

Description automatically generated

On the right side of the module, we will insert the following code:

What this code does when we run it, is that it automatically adds another workbook, opens it, and then automatically positions us to this new workbook.

Save New Workbook in Vba

We need to add another line in our code to save the new workbook. This will be the line:

As the newly created workbook will automatically be active, we will simply save it, but we will also define the file name (Mybook), Location (Desktop), and type of the file (xlsx). This is what our code looks like in the module: 

Graphical user interface, text, application, email

Description automatically generated

When we run the code by pressing F5 while in the module, a new workbook will automatically be added and saved on the desktop, with the “Mybook” name

Graphical user interface, text, application

Description automatically generated

Choose a Folder for Saving New Workbook

There is an additional way to save the new workbook, i.e. to allow us to choose a destination folder. This will be the code for that purpose: 

This code declares an additional variable called ws, as a string, and then it calls for the GetSaveAsFilename option, which has several parameters. For our example, we decided to go with InitialFileName and filefilter parameters, in which we declared the initial name and possible types in which the file can be saved. 

When we execute the code, this is the window that we will see:

Graphical user interface

Description automatically generated

It is noticeable that we are now prompted to default Office folder when saving files (Documents) and that we can save our file as a .xlsx file (Excel Workbook) or .xlsm (Macro Enabled) file. Our file name is equal to “Default” which we also defined in the code. We can change it to our liking.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba