9 Different Ways to Create New Sheets in Excel VBA

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.

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA Image_01

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

See the image below:-

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA Image_02

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.

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA Image_03

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.

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA_Image04

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.

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA_Image06

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.

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA

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.

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA

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.

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA

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:-

Create New Sheets in Excel VBA
Create New Sheets in Excel VBA

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

5 Ways to Delete Blank Rows in Excel (With VBA Macro)

Leave a Comment

Your email address will not be published.