8 Examples to Add Sheet in Excel by VBA

How to add Excel sheet by VBA code

• By using VBA, you may add one or more Excel sheets easily.
• A sheet can be added at the beginning, end, or in between i.e. at the specified position
• You may name the newly added sheet as well
• The added sheet becomes active

Which method is used to create new sheet in VBA

A new sheet can be added by Sheets object’s Add method. For example,

Sheets.Add

This code will add a sheet before the active sheet in your Workbook.

Let us look at a few examples with macros and images for adding sheets.

Add a sheet before active sheet example

In the first example, we will add a sheet before the existing active sheet. For an idea, this is our sample sheet before we execute the code:

Follow these steps for adding a new sheet before the active sheet:

Step 1:

Go to the “DEVELOPER” tab and locate/click “Visual Basic” as shown below:

It should open the VBA code editor.

Or press “Alt + F11”shortcut.

Step 2:

In the VB editor, “Insert” a new module:

Write this line of code in the module to add a new sheet:

Sub Add_Sheet()

Sheets.Add

End Sub

Step 3:

Save the module (it may ask to name the module)

Step 4:

Run the code by pressing F5 or from this menu:

This should add a new sheet before “Sheet1” as this was our active sheet:

Adding a sheet with specified name

To name a newly added sheet, use the Name as follows:

Sheets.Add.Name = “New Sheet Name”

In the example below, we will add a new sheet to our sample Workbook with the name “Product Information”.

See the VBA code and output below:

Sub Add_Sheet()

Sheets.Add.Name = "Product Information"

End Sub

Result Workbook:

As “Sheet3” became active sheet, so “Product Information” sheet was added before it.

An example of adding named worksheet at the end of Workbook

Though there is no straight-forward method to add a Worksheet at the end of an active Workbook, you may do this by getting the total count of existing sheets and then adding a new sheet at the end.

The example below shows how.

Sub Add_Sheet_End()

'Variable to get maximum sheet number

Dim Max_sheet

Max_sheet = Sheets.Count

'Adding after maximum sheet

Sheets.Add After:=Sheets(Max_sheet)

End Sub

Adding custom name sheet at the end

To add a custom-named sheet at the end, you may use this code and replace the name with your own. We will add “Product Information” sheet at the end.

VBA code:

Sub Add_Sheet_End()

'Variable to get maximum sheet number

Dim Max_sheet

Max_sheet = Sheets.Count

'Adding at the end with custom name

Sheets.Add(After:=Sheets(Max_sheet)).Name = "Product Information"

End Sub

Result:

Add name sheet at the beginning

Creating a sheet as the first sheet while any of the sheets is active is also simple:

The code:

Sub Add_Sheet_Start()

Sheets.Add(Before:=Sheets(1)).Name = "Sales Information"

End Sub

Output:

Adding a Worksheet after a specific sheet

The following example shows adding a new Worksheet after a specified Worksheet. Suppose, we have the following sample Workbook sheets:

We want to add a new sheet after “Sheet10”.

Execute this code:

Sub Add_Sheet()

Sheets.Add After:=Sheets("Sheet10")

End Sub

Result:

You can see, “Sheet11” is added after “Sheet10”.

And to name the sheet as you want for the specified position:

Sub Add_Sheet_Name()

Sheets.Add(After:=Sheets("Sheet10")).Name = "Sales Sheet"

End Sub

Result:

You can see, we provided the name “Sales Sheet” and it is added after Sheet10 in our Workbook.

Adding before a specified sheet example

Just replace, “After” with “Before” in the above example, and a new sheet with the specified name will be added before. See the code and output:

VBA code:

Sub Add_Sheet_Name()

Sheets.Add(Before:=Sheets("Sheet10")).Name = "Employees Sheet"

End Sub

Result:

Adding multiple sheets after specified position

Multiple sheets can also be added by a single line of code in VBA.

Just specify the count and position as shown in the example below.

We are adding 3 sheets after “Sheet13” in our sample Workbook:

VBA code:

Sub Add_Sheet_multiple()

Sheets.Add After:=Sheets("Sheet13"), Count:=3

End Sub

Result: