VBA Create Worksheet in Excel

VBA Create Worksheet in Excel.Create new Worksheet in a Workbook using Excel VBA. Where Worksheet represents an object. The Worksheet object is part of sheets collection. Add is a method to Create New Worksheet in a Workbook in Excel VBA. When we Create New Worksheet in a Workbook default name of worksheet is Sheet1, Sheet2, …, SheetN. Where N represents the next available number. We can create new sheet before the active sheet, after the active sheet, end of all sheets, beginning of all sheets, or we can specify particular sheet before or after. Default newly added sheet created before the active Worksheet.

Syntax for Create New Worksheet in a Workbook

Here is the following syntax to Create a new Worksheet in a Workbook. We use add method to create new sheet. Default newly inserted sheet created or added before the active Worksheet.

Sheets.Add(Before, After, Count, Type)

Where Before is an optional argument. It represents the sheet object. It is used to insert a new sheet before a specific sheet.
After is an optional argument. This represents the Worksheet object. It is used to add a new sheet after a specific sheet.
Count is an optional argument. It helps to specify number of sheets to be added.
Type is an optional argument. This parameter specifies the type of sheet to be added. It represents one of the following XlSheetType constants. i.e xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. The default value is xlWorksheet.
you can refer following XlSheetType enumeration in Excel. You can know its value and description.

Link: https://docs.microsoft.com/en-us/office/vba/api/excel.xlsheettype

Macro to Create New Worksheet in a Workbook using Excel VBA

Let us see the following macro to Create New Worksheet in a Workbook. In the following example Add method helps to Create New Sheet in a Workbook. Default name is Sheet1. Now the newly added sheet is active sheet.

'VBA Create New Worksheet or Sheet in a Workbook
Sub VBAF1_Create_New_Worksheet()
      
    'Insert or create new Sheet
    Sheets.Add

End Sub

Code to Add New Worksheet with Name using Excel VBA

Here we will see how to add new Sheet with name in a Workbook. You can specify your own sheet name. In the following example i have specified a name ‘NewSheetName’.

'VBA Create New Sheet with name in a Workbook
Sub VBAF1_Create_New_Worksheet_With_Name()
      
    'Add new Sheet with name
    Sheets.Add.Name = "NewSheetName"  

End Sub

Insert New Worksheet with Name using object in Excel VBA

Let us see see how to add new Sheet with name using object in a Workbook. You can specify your own sheet name. In the following example i have specified a name ‘NewSheetName’.

'VBA Create New Sheet with name in a Workbook
Sub VBAF1_Create_New_Worksheet_With_Name()
      
    'Add new Sheet with name
    Sheets.Add.Name = "NewSheetName"  

End Sub

Add New Worksheet and Specify Name from a Cell and using VBA

You can create new Sheet and assign name. You can assign name to sheet either directly or it can be taken from the specified cell. Here is an example for your reference.

'VBA Add New Worksheet and Specify Name from Cell
Sub VBAF1_Add_Sheet_Specify_Name_From_Cell()
      
    'Specify Name to Sheet from cell
    Sheets.Add.Name = Sheet1.Range("B1")

End Sub

VBA Code to Add Multiple Sheets to a Workbook

We can add or insert multiple sheets to a Workbook using VBA in Excel. Here we use count parameter to specify number of sheets to be added. Have a look following example. In this example active sheet name is Sheet1 and new sheet names are Sheet2, Sheet3, Sheet4 and Sheet5.

'VBA Add Multiple Sheets to a Workbook
Sub VBAF1_Add_Multiple_Worksheet()
      
    'Insert or create new Sheet
    Sheets.Add Count:=4

End Sub

Add New Sheet After a Specific Sheet in a Workbook using Excel VBA

Let us see how to add new sheet after a specific sheet in a Workbook using VBA.

'VBA Add New Sheet After a Specific Sheet
Sub VBAF1_Add_New_Sheet_After_Specific_Sheet()
      
    'Insert or create new Sheet
    Sheets.Add(after:=Sheets("Sheet3")).Name = "AfterSheet"

End Sub

Insert New Sheet Before a Specific Sheet in a Workbook using Excel VBA

The following example shows how to insert new sheet before a specific sheet in a Workbook using VBA in Excel.

'VBA Insert New Sheet Before a Specific Sheet
Sub VBAF1_Insert_New_Sheet_Before_Specific_Sheet()
      
    'Insert or create new Sheet
    Sheets.Add(before:=Sheets("Sheet3")).Name = "BeforeSheet"

End Sub

Add a New Sheet at the Beginning of all Sheets in a Workbook using Excel VBA

Let us see the following example how to insert new sheet at the Beginning of all Sheets in a Workbook using VBA in Excel.

'VBA Add New Sheet at the Beginning of All Sheets
Sub VBAF1_Add_New_Sheet_Beginning_of_All_Sheets()
      
    'Insert or create new Sheet
    Sheets.Add(before:=Sheets(1)).Name = "FirstSheet"

End Sub

Insert New Sheet at the end of all Sheets in a Workbook using Excel VBA

Here is an example it helps to insert new sheet at the end of all sheets in a Workbook using Excel VBA.

'VBA Insert New Sheet at the end of All Sheets
Sub VBAF1_Insert_New_Sheet_End_of_All_Sheets()
      
    'Insert or create new Sheet
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = "LastSheet"

End Sub

Error: Run-time error ‘1004’

Some times we see run time errors. Let us see an example. We are adding new sheet with name as “NewSheet”. It creates new sheet with specified name. If we run again same macro it throws following error as shown in the screenshot. Because, we can’t create multiple sheets with same sheet name.

'VBA Insert New Sheet at the end of All Sheets
Sub VBAF1_Insert_New_Sheet_End_of_All_Sheets()
      
    'Insert or create new Sheet
    Sheets.Add.Name= "NewSheet"

End Sub

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