How to Add New Sheets in Excel Using VBA

By /

Adding new sheets in Excel using VBA is a simple process. All you need to do is set up a loop and use the ActiveWorkbook.Worksheets.Add method to add a new sheet in each iteration. Additionally, you can set the sheet name, tab color, and position of the new sheet using the Name, Tab.ColorIndex, and Before properties of the Worksheets object. After the loop is finished, the new sheets will be added to the workbook.

You can use the following methods in VBA to add new sheets to an Excel workbook:

Method 1: Add New Sheet

Sub AddSheetToWorkbook()
Sheets.Add
End Sub

This macro will add one new sheet to the workbook with an auto-generated name that describes which sheet number it represents in the workbook.

For example, if there are three sheets in the workbook and you run this macro then a new sheet with the name Sheet4 will be created.

Note: By default, VBA will add the new sheet immediately before the currently active sheet.

Method 2: Add Multiple New Sheets

Sub AddSheetToWorkbook()
Sheets.Add Count:=3
End Sub

This macro will add three new sheets to the workbook.

Method 3: Add New Sheet with Name

Sub AddSheetToWorkbook()
Sheets.Add.Name = "MyNewSheet"
End Sub

This macro will add one new sheet to the workbook with the name MyNewSheet.

Method 4: Add New Sheet with Name Before Specific Sheet

Sub AddSheetToWorkbook()
Sheets.Add(Before:=Sheets("Teams")).Name = "MyNewSheet"
End Sub

This macro will add one new sheet to the workbook with the name MyNewSheet directly before the existing sheet called Teams.

Method 5: Add New Sheet with Name After Specific Sheet

Sub AddSheetToWorkbook()
Sheets.Add(After:=Sheets("Teams")).Name = "MyNewSheet"
End Sub

Method 6: Add New Sheet with Name At End of Workbook

Sub AddSheetToWorkbook()
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "MyNewSheet"
End Sub

This macro will add one new sheet to the workbook with the name MyNewSheet at the very end of the workbook.

Method 7: Add New Sheet with Name At Beginning of Workbook

Sub AddSheetToWorkbook()
Sheets.Add(Before:=Sheets(1)).Name = "MyNewSheet"
End Sub

This macro will add one new sheet to the workbook with the name MyNewSheet at the very beginning of the workbook.

The following examples show how to use each method in practice with the following Excel workbook that has three existing sheets:

Example 1: Add New Sheet

We can create the following macro to add one new sheet to the workbook with a name that is auto-generated:

Sub AddSheetToWorkbook()
Sheets.Add
End Sub

When we run this macro, a new sheet is added to the workbook with the name Sheet4 directly before the Teams sheet, which we had selected when we ran the macro:

Example 2: Add Multiple New Sheets

We can create the following macro to add three new sheets to the workbook with names that are auto-generated:

Sub AddSheetToWorkbook()
Sheets.Add Count:=3
End Sub

When we run this macro, three new sheets are added to the workbook directly before the Teams sheet, which we had selected when we ran the macro:

Example 3: Add New Sheet with Name

We can create the following macro to add a new sheet to the workbook with a specific name:

Sub AddSheetToWorkbook()
Sheets.Add.Name = "MyNewSheet"
End Sub

When we run this macro, a new sheet named MyNewSheet is added to the workbook directly before the Teams sheet, which we had selected when we ran the macro:

Example 4: Add New Sheet with Name Before Specific Sheet

We can create the following macro to add a new sheet to the workbook directly before a specific existing sheet:

Sub AddSheetToWorkbook()
Sheets.Add(Before:=Sheets("Teams")).Name = "MyNewSheet" 
End Sub

When we run this macro, a new sheet named MyNewSheet is added to the workbook directly before the Teams sheet:

Example 5: Add New Sheet with Name After Specific Sheet

We can create the following macro to add a new sheet to the workbook directly after a specific existing sheet:

Sub AddSheetToWorkbook()
Sheets.Add(After:=Sheets("Teams")).Name = "MyNewSheet" 
End Sub

When we run this macro, a new sheet named MyNewSheet is added to the workbook directly after the Teams sheet:

Example 6: Add New Sheet with Name At End of Workbook

We can create the following macro to add a new sheet to the very end of the workbook:

Sub AddSheetToWorkbook()
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "MyNewSheet"
End Sub

When we run this macro, a new sheet named MyNewSheet is added to the very end of the workbook:

Example 7: Add New Sheet with Name At Beginning of Workbook

We can create the following macro to add a new sheet to the very beginning of the workbook:

Sub AddSheetToWorkbook()
Sheets.Add(Before:=Sheets(1)).Name = "MyNewSheet"
End Sub

When we run this macro, a new sheet named MyNewSheet is added to the very beginning of the workbook:

Note: You can find the complete documentation for the Sheets.Add method .

x

Related terms:

  1. How to Add New Line to Message Box in VBA (with Example)
  2. How to add days to a date in VBA?
  3. Delete Files Using VBA (With Examples) How to use VBA to delete files
  4. How do I convert a string to a long number in VBA?
  5. How to add blank columns in Google Sheets Query
  6. How do I add a second Y-Axis in Google Sheets
  7. How to Add Months to Date in Google Sheets (With Examples)
  8. How can I add a calculated field to a pivot table in Google Sheets?
  9. How to Add Trendline to Chart in Google Sheets (Step-by-Step)
  10. How to add labels to scatterplot points in Google Sheets