Add And Save New Workbook Using VBA In Microsoft Excel 2016

Hi there! So, now you need to create a new workbook and save workbook using VBA Macro. this is good. I like the way you think.

In this article, we will learn how to add a new workbook and save it using Excel vba.

VBA code to add a workbook

There are two methods to add a workbook. One is recommended and the other one is not.

'Not recommended Macro code: use this when you don’t want to save 'this new workbook.---------------------Sub AddWorkbook()

Workbooks.Add 'creates a new workbook instantly

End Sub

Here we simply added a new workbook. It will create workbook named book1, book2, book3 and so on.

'Recommended: use this macro when you want to save the added workbook.---------------------Sub AddWorkbook()

Dim WB as Workbook 'creates a reference to workbook object
Set WB = Workbooks.Add 'adds a workbook.

End Sub

In this code, we first created an reference to a workbook object. And then we initialised it with a new workbook object. Benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc.

VBA code to add and save a workbook

This code just saves the workbook at a default location with the default name.

Sub AddWorkbook()Dim WB As Workbook 'creates a reference to workbook object
Set WB = Workbooks.Add 'adds a workbook.WB.Save 'saves workbook at the default location

End Sub

There will be times when you would want to save your added workbook with a specified name and save it at a definite location. The below code adds a workbook and saves it at a defined location.

Sub AddWorkbook()Dim WB As Workbook 'creates a reference to workbook object
Set WB = Workbooks.Add 'adds a workbook.WB.SaveAs "D:\VBA added File\myfile.xlsx" 'saves at the given file location and name

End Sub

Here, we used the save as command of Excel using VBA. SaveAs is a property/function of Workbook class. This is used when you want to rename your workbook using excel VBA macro.

That’s it. It is the code to add a new workbook using VBA in Excel 2016. SaveAs runs VBA save as command on excel.

Simple. Wasn’t it? It is. Let me know your thoughts and doubts in the comments sections.
Download file

Related Articles:

Split Excel Sheet Into Multiple Files Based On Column Using VBA

Change The Default Printer Using VBA in Microsoft Excel 2016

Turn Off Warning Messages Using VBA in Microsoft Excel 2016

Display A Message On The Excel VBA Status Bar

Insert Pictures Using VBA in Microsoft Excel 2016

How To Loop Through Sheets In Excel Using VBA

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel



  1. The macro hangs up on the fname = InputBox(“What is the filename? (without extension)”) line with syntax error message.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.