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.
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 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.
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
How to Use SUMIF Function in Excel
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.
Getting an error like "method or data member not found" highlighting Add method. Can you please help me
Let us see the error containing line please...
The macro hangs up on the fname = InputBox(“What is the filename? (without extension)”) line with syntax error message.
let me see the whole code...