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.
- Overview of VBA New Workbook using Excel
- Syntax for Create New Workbook Method
- Macro to Create New Workbook With default number Of Sheets in Excel VBA
- Procedure to Create New Workbook With Several number Of Sheets in Excel VBA
- Macro to Create New Workbook using template in Excel VBA
- Procedure to Create New Workbook with one Worksheet in Excel VBA
- VBA Code to Create New Workbook with one Chart Sheet in Excel VBA
- Instructions to use Macro
- Related Workbook Object articles
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.
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
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
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
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
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
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.
You may also like the related Workbook Object articles.