This blog post explains the multiple ways to create new sheets in Excel VBA.
In Excel, when we create a new sheet, we generally use the keyboard shortcut “SHFT+F11”.
And new sheet will be inserted by default and always before the active worksheet and its default sheet name i.e. Sheet2, Sheet3, Sheet4, and so on.
But VBA gives us more freedom to insert a new sheet in multiple ways.
Like creating a sheet with a name or creating a sheet before/after any particular sheet.
So in this post, we will learn the various methods to create a new sheet in Excel VBA.
In VBA, we have the below code to create the default sheet in Excel.
Executing this code and it will create a new sheet in Excel.

But when we press the spacebar just after the code, see the below image.

After typing Sheets. Add when you press ‘Spacebar’ it will give you more options to add a new sheet.
See below for an explanation of these tooltip options.
Before: = use this when to create a sheet before any particular sheet
After: = use this when to create a sheet after any particular sheet
Count: = use this when creating multiple sheets.
Type: = this will ask to add sheet type
Insert a Default Sheet
If we want to create a single default sheet in Excel with the help of VBA, we will use the below VBA code:-

Executing this VBA code, Excel will create a default sheet just before the existing sheet.
Add Multiple Sheets
If you want to create multiple sheets in Excel, then this VBA code will create multiple sheets before the active sheet.
See in the below image only a single sheet appears in Excel.

But after executing the below VBA code, it will create the 3 numbers of the sheet before Sheet1.
See the image below:-

So this way we can add multiple sheets in Excel by VBA.
Create New Sheets in Excel VBA with Name
If we want to create a new sheet with the name “New Data” then we need to use the below VBA code.

Use below VBA code:-
Sub NewSheet_Add() Sheets.Add.Name = “New Data” End Sub |
See in the image, that a new sheet “New Data” is created before the active or current sheet.
Create New Sheet Before Existing Sheet
When we create a new sheet in Excel, is always created before the active or current sheet by default.
But VBA gives us more options to create a new sheet before or after any particular sheet.
To create a sheet in VBA we use the code as follows:-

When we press a single spacebar after this code, it will show us a tooltip with more options.
Such as creating a new sheet before any particular sheet.
Or create a new sheet just after any particular sheet.
See the tooltip in the above image circled area.

We will use this tooltip to create a sheet before or after any particular sheet.
So, with the help of the above tooltip, we will create a new sheet before “MyData” the existing worksheet.
We will use the below VBA code to Create a sheet before the existing sheet.

Use the below VBA code:-
Sub Create_New_Sheet_Before() Sheets.Add(Before:=Sheets(“MyData”)).Name = “MyNewData” End Sub |
See in the above image, that a new sheet named “MyNewData” is created before the existing sheet “MyData”.
Create New Sheet after Existing Sheet
Now we will create a new sheet after the existing sheet “MyData” with the help of VBA code.

See the above VBA code we have created a new sheet “NewSheet” just after the “MyData” existing sheet.
Use the below VBA code:-
Sub Create_New_Sheet_After() Sheets.Add(After:=Sheets(“MyData”)).Name = “NewData” End Sub |
Create Sheet at the Beginning
If we have multiple worksheets and want to create a new sheet at the beginning of all the sheets.
The above VBA code will create a new sheet before all the worksheets, in the above image.

Use the below VBA code:-
Sub Create_Sheet_At_Beginning() Sheets.Add(Before:=Sheets(“Sheet1”)).Name = “First Sheet” End Sub |
Create Sheets at the End of Sheets
With the help of the VBA code, we will create a new sheet at the end of all the sheets.

See in the above image where we have created the sheet at the end of all sheets.
Use the below VBA code:-
Sub Create_Sheet_At_End() Sheets.Add(after:=Sheets(Sheets.Count)).Name = “Last Sheet” End Sub |
Create New Sheet from a Cell Value
VBA also gives us the freedom to create a new sheet from a cell value within a worksheet.
See in the below image where we have created a new sheet with the help of a cell (“A1”) in the worksheet.

A new sheet “Employee Data” is created now.
Use below VBA code:-
Sub Create_Sheet_From_Cell_Value() Sheets.Add.Name = Range(“A1”).Value End Sub |
Create sheets from the list in Excel
Now suppose that we have a list in an Excel sheet and we want to create a sheet with these names.
See the names from Range (“A1:A4”)
Use this list to create new sheets in VBA, see the image below:-

Use the below VBA code:-
Sub Add_Sheets_From_List() Dim sheets_count As Integer Dim sheet_name As String Dim i As Integer sheet_count = Range(“A1:A4”).Rows.Count For i = 1 To sheet_count sheet_name = Sheets(“Sheet1”).Range(“A1:A4”).Cells(i, 1).Value Worksheets.Add().Name = sheet_name Next i End Sub |
Here we have learned the multiple ways to create a new sheet in Excel VBA.
If you want to learn more about this, please visit Microsoft Office Support.
I hope this VBA tutorial will help you.
Please feel free to put any comment or suggestion in the comment box below.
Thanks
Other Related Post
How to Create Search Box in Excel (No VBA Code Required)
VBA Macro to Dynamically Copy and Paste from One Sheet to Another
How to Lock Scroll Area in Excel with VBA (Step By Step Guide)
How to Hide Blank Rows in Excel Using VBA Macro
3 Quick Ways to Move or Copy a Worksheet into Another Workbook
3 Quick Ways to Change the Font Color in Excel VBA
How to Split Cells Diagonally in Excel (Step by Step Guide)
3 Easy Ways to Select the Visible Cells Only In Excel
How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel