Add and Save new Workbook using VBA in Microsoft Excel

Some vba macros / codes require a new workbook to be created and then saved during the course of the macro. We can do this very easily with a small macro.

Option Explicit

Sub Macro1()

Dim Wk As Workbook

Set Wk = Workbooks.Add
Application.DisplayAlerts = False
Wk.SaveAs Filename:=”B:\Test1.xlsx”
Application.DisplayAlerts = True

End Sub

 

This code is simple –

Dim Wk As Workbook
First we declare Wk as a workbook.

Set Wk = Workbooks.Add
Then we add a new workbook through code which is assigned to Wk.

img1

Application.DisplayAlerts = False

We set display alerts to false and save the file with the required file name. And then set the display alerts to true.

Wk.SaveAs Filename = “B:\Test1.xlsx”

If you have other code to include in this, you can either include it before saving the file or after. You can include other actions which need to be performed on the workbook and then save it.
If you want to prompt the user to give the filename then you need to modify the code a little. The new code is –
img2
We can see in the above screenshot, that the file has been saved with the name “Test1.xlsx”.

Option Explicit

Sub Macro2()

Dim Wk As Workbook
Dim fname As String

Set Wk = Workbooks.Add
Application.DisplayAlerts = False
fname = InputBox(“What is the filename? (without extension)”)
Wk.SaveAs Filename:=”B:\” & fname & “.xlsx”
Application.DisplayAlerts = True

End Sub

Dim fname As String
Here we have an additional variable known as fname which will save the value given by the user for the filename.

fname = InputBox (“What is the filename? (without extension)”)
The user is asked for the filename without extension through the inputbox. See the picture of the inputbox which appears.

img3

The text entered in the inputbox is saved in the variable “fname” which is included in the SaveAs code line. Wk.SaveAs Filename:=”B:\” & fname & “.xlsx”.

 

Using above macro we can activate new workbook without closing current workbook.

image 29

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube