VBA Create Workbook in Excel

Create Workbook in Excel VBA. 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 for Create New Workbook Method

Here is the following syntax to create new Workbook.

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 New Workbook With default number Of Sheets in Excel VBA

Let us see the following macro to create new Workbook With default number Of Sheets. In the following example Add method helps to create new workbook. Default name is Book1 with default number of sheets.

'VBA 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 macro to create new Workbook With several number Of Sheets. It creates new workbook with 5 Worksheets.

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

Macro to Create New Workbook using template in Excel VBA

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

'VBA Create New Workbook from Template File in Excel
Sub VBA_Create_New_Workbook_From_Template_File()
         
    'Variable declaration
    Dim sFile As String
         
    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 new workbook with one worksheet in Excel 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

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

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

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

Instructions to use Macro

Here are the instructions to use above macro procedure in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above output screenshot of the specified procedure.

Related Workbook Object articles

You may also like the related Workbook Object articles.

VBA Workbook Object




Leave a Comment

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