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:

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:

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.

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:

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:

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:

Result:

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

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

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:

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:

Result: