8 Examples to Add Sheet in Excel by VBA

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


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

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,


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:



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:



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:



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

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



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:



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: