VBA to create new Excel Workbook

You can easily add any type of workbook, whether it’s a .xlsm, xlsx, xlst etc., using the Method workbooks.add

Using this vba method, one can achieve as follows:

You can add a workbook.add method to a variable:

Dim wb as workbook
set wb=workbooks.add 

can process creating a new workbook and manipulate the created workbook as desired. name a workbook and save file.

To do this we have to proceed with the following code:

Public Sub createnewWorkBook()
     Dim newWorkbook as object
     Set newWorkbook = Workbooks.Add
    With newWorkbook
        .Title = "Set workbook title - displayed in Info > Properties"
        .Subject = "Subject related  - displayed in Info > Properties"
        .SaveAs Filename:="C:\Users\SAFT\Desktop\New workbook.xlsx" 'name a file
    End With
End Sub

You can even use Workbooks.Add method to copy data from a workbook to a new worksheets.

Public Sub extractColumn()
Set range1 = Range("A:F, BI:GI, BQ:CQ,CL:CL,CM:CN,CT:CT,DM:DM")
Set newbook = Workbooks.Add
ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub

Syntax of Workbooks.Add Method

Workbooks.Add(Template as Object)

Template Optional Object. Determines how the new workbook is created. If this argument is a string specifying the name of an existing Microsoft Excel file, the new workbook is created with the specified file as a template.


The new workbook is created with the specified file as a template:

Dim strTemplate As String: strTemplate = "c:\temp\myfile.xlsm"
Dim wb As Workbook
Set wb = Workbooks.Add(strTemplate)

The new workbook is a full copy of the file which served as template – including VBA code if any. The new workbook however does not yet exist in the file system.

The new workbook’s name will be after the template with a sequential number added, e.g. here ‘myfile1’.

If this argument is a constant, the new workbook contains a single sheet of the specified type.Can be one of the following XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet.
If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property). If the Template argument specifies a file, the file name can include a path.


Workbooks.Add is a Method (a Function of an Object), it returns a Workbook Object. You can visually see a workbook opened after using the Method, and you can also define a workbook Object to get the returned Workbook.

Set NewBook = Workbooks.Add

Published by Bandi Fouziya

Hi, I am a VBA Developer, working on automating office apps. I have 16+ years of experience in developing visual basic applications for the clients around the globe. I have worked on many complex projects of which are: 1. Creating Interactive Dashboards 2. Bulk web Data Scraping 3. Automating web app task from Excel through API integration with VBA 4. Mailchimp Automation 5. Real Time Indicators Statistical Analysis 6. Access database application development - multi user platform with sql server as backend 7. Statistical Analysis using R and R shiny apps through VBA and many more. I am passionate in learning new tools and techniques and would love to share with you all. Join in hand to learn smart tools. Apply in your life and be more productive. Check out my profile on Upwork: https://www.upwork.com/freelancers/~01f0ac790f1b3265ff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: