How to add a new worksheet using Excel VBA

vba-add-newsheet

In this tutorial, we are going to see how to add a new worksheet, and also how to rename it using Excel VBA.

Adding a new worksheet in VBA is so easy, and we can add it in two ways using Sheets.add or Worksheets.add.

Both methods do create a new worksheet with a default name and location. Here location means the order where the new sheet is inserted. By default, Excel generates a new sheet before the active sheet.

For example, If "Sheet1" is active, by default excel adds the new sheet before "sheet1" with the default name "Sheet2".

Having said that, we can customize these order of placement through "after" and "before" properties.Let us see these properties with an example,

After:=

As said before Excel by default adds a new sheet before the active sheet in order to override this "after=" property can be used. The main purpose of this property is that it adds a new sheet after the specific sheet name we specify.

Code: Sheets.Add after:=Sheets("Sheet1")

vba-add-newsheet

As you can see the above code added a new sheet after Sheet1.

Before:=

This property inserts a new worksheet before the sheet we specify. In this case, it added a new sheet before sheet1 with default name sheet3 as we did not assign any name for it.

Code: Sheets.Add before:=Sheets("Sheet1")

vba-add-newsheet

As you can see the above code added a new sheet before Sheet1.

Rename worksheet:

The name property is used to set the name for worksheets. In this example, we are changing the name sheet1 to Duplicate.

Code: Sheets("Sheet1").Name = "Duplicate"

vba-add-newsheet

As you can see the above code renamed Sheet1 as Duplicate.

Add a new worksheet with name:

First, we should create a new sheet, in this case, am creating a new sheet after the duplicate sheet and am assigning a name for the active sheet.

Sheets.Add after:=Sheets("Duplicate") //create a new sheet after Duplicate Sheet
ActiveSheet.Name = "Withname" //Assign a name for newly created sheet

vba-add-newsheet

As you can see the above code created a new worksheet and named it.

Note: These codes won't work if executed the second time. For example in the last part, we created a new sheet and named it as with name if run it the second time you will get an error because already there is a sheet with the same name.

In that case, we should check if there is a sheet already available with the same name.

Add worksheet after last sheet:

Sheets.Add After:=Sheets(Sheets.Count) //create a new sheet after Last Sheet

The above code adds a new worksheet to the last of all the sheets available in the workbook. In this we use sheets. count instead of sheet name itself as we did in earlier cases. Basically what sheets.count does is it counts the total number of sheets available in a workbook.

Suppose we have 7 sheets then sheets.count will be 7. So after the 7th sheet, a new sheet will be added.

ActiveSheet.Name = "Last" //Assign a name for newly created sheet

vba-add-newsheet

Hope you liked this article, if so please share it with your friends.

Excel Logo

Download project file

Share on:
Most Popular