VBA Create Workbook in Excel

VBA Create Workbook in Excel. Where Workbook represents an object. It is part of workbooks collection. Add is a method to create new workbook in Excel VBA. When we create new workbook default name of workbook is Book1, Book2, … BookN. Where N represents the next available number.

Syntax to Create Workbook in Excel using VBA

Here is the following syntax we can see how to create new Workbook in Excel VBA. We use add method to create a workbook in Excel.

Workbooks.Add
'or
Workbooks.Add([Template]) As Workbook

Where Template is an optional argument. It represents how the new workbook to be create. If we specify this argument as string(not a constant), then the new Workbook is created with the specified file as a template.

If an argument is constant, then the new workbook consists of either one Worksheet or One Chart. The constant can be one of the following xlWBATChart, xlWBATWorksheet, xlWBATExcel4IntlMacroSheet, or xlWBATExcel4MacroSheet. These constants are called XlWBATemplate constants.

Macro to Create Workbook With default number Of Sheets in Excel VBA

Let us see the following VBA code to create new Workbook with default number Of Sheets. Usually new workbook default contains 3 worksheets. In the following example Add method helps to create new workbook. Default name is Book 1 with default number of sheets.

'VBA code to Create New Workbook with default number of Worksheets in Excel
Sub VBA_Create_New_Workbook()
         
    Workbooks.Add
     
End Sub

Procedure to Create New Workbook With Several number Of Sheets in Excel VBA

Let us see the following code to create Workbook which consists of several number Of Sheets. It creates new workbook with 5 Worksheets.

'VBA Create Workbook With has several number Of Worksheets in Excel
Sub VBA_Create_New_Workbook_With_Specified_Sheets()
         
    Workbooks.Add
    Worksheets.Add Count:=5
     
End Sub

Output: Here is the output screenshot of above VBA code for your reference.

Macro to Create New Workbook using template in Excel VBA

Here is the following example to create workbook from the template file in Excel using VBA.

'VBA Create Workbook from the Template File in Excel
Sub VBA_Create_New_Workbook_From_Template_File()
         
    'Variable declaration
    Dim sFile As String
    
    'File path - you can change it
    sFile = "D:\VBAF1\VBA Blog Posts.xlsm"
    
    Workbooks.Add (sFile)
     
End Sub

Procedure to Create New Workbook with one Worksheet in Excel VBA

Let us see how to create workbook with only one worksheet in Excel using VBA.

'VBA Create New Workbook using template constant xlWBATWorksheet in Excel
Sub VBA_Create_New_Workbook_with_template_Worksheet_constant()
         
    Workbooks.Add (xlWBATWorksheet)
     
End Sub

Output: Here is the output screenshot of above VBA code for your reference.

VBA Code to Create New Workbook with one Chart Sheet in Excel VBA

Here is one more example to create new workbook with only one chart in Excel using VBA code.

'VBA Create New Workbook template constant xlWBATChart in Excel
Sub VBA_Create_New_Workbookwith_template_ChartSheet_constant()
         
    Workbooks.Add (xlWBATChart)
     
End Sub

Output: Here is the output screenshot for your reference.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers