Working with Worksheets in VBA Excel

In one of our previous articles, we learned about Collections. Today we will learn about one of the most used and important collections that is Worksheets in VBA.

So let us begin.

Worksheets Collection in VBA

Worksheets are one of the frequently used VBA objects to work with. Every Worksheet has methods as well as properties. All the worksheets of the workbook are contained in the Worksheets Collection. In other words, we can say that a Worksheet Collection refers to a grouped collection of all the worksheet objects in the workbook.

We can easily access each worksheet in the workbook with the help of the Worksheet Index or using the name of the worksheet.

Difference Between Sheets and Worksheets Collection

People are often confused with Sheets and Worksheets Collection assuming that both are the same but this is not true. Worksheets Collection contains Worksheets whereas Sheets Collection contains Worksheets as well as Chart sheets.

We would use the Sheets Collection only when we want to refer to the chart sheets as well otherwise we use the Worksheets Collection. Sheets and Worksheet collection make no difference when there exist no chart sheets in our workbook.

worksheets collection in vba

This Workbook has three worksheets Sheet1, Sheet2, Sheet3, and one Chart Sheet Chart1. Therefore. The Sheets collection contains four Sheet objects whereas the Worksheet collection contains three Worksheet objects.


worksheets in vba infographics

Referring to the Worksheet using VBA

There are three ways to refer to a Worksheet object using Worksheets Collection.

Referencing by Index

In this method, we specify the index of the worksheet we want to refer to, in the Worksheets Collection. The index for the first worksheet is 1, for the second worksheet is 2, and so on. Chart Sheets are simply ignored by the Worksheets Collection.


For instance, if the workbook contains the following worksheets, then we can activate Sheet2 using the index as 2.

worksheets collection in vba
Sub activate_sheet2()
End Sub

Put this Sub Procedure in the VBE and run it.

activate a worksheet using vba in excel

Sheet3 was active sheet. When you run this Sub Procedure, Sheet2 gets activated.

Referencing by Name

We can use the name of the worksheet to refer to it using the Worksheets collection. Let us say we have three worksheets as follows.

activate a worksheet using its name

To activate the sheet named Jan, use the following Sub Procedure.

Sub activate_Jan()
End Sub

This method has the disadvantage of the risk of change of sheet name. This code would break if someone changes the name of the worksheet to something else.

Referencing using Code Name

Each Worksheet object has a code name that cannot be changed by the user. We can use the code name in our code so that even if someone changes the name of the worksheet, the code still works.

To change the code name of the worksheet, follow these steps.

  • Press Alt and F11 keys to open the VBE.
  • Press the F4 key or go to the View tab and choose the option Properties Window to open the Properties window.
  • Click on the name of the Sheet whose code name you wish to change and add a new code name in the properties window.
change the code name for worksheet

We set the code name to January. You can now activate this Worksheet using the code name.Activate method.

Sub activate_Jan()
End Sub

The advantage is that this code works fine even if we change the name of the worksheet.

change the code name for worksheet example

Adding a Worksheet using VBA

We can add a Worksheet with the help of Add method of the Worksheets collection.


This line of VBA code will add a new worksheet with the default name. We can also specify the Before argument to add a new worksheet before a specific worksheet of the workbook.

Let us suppose we have three worksheets in the workbook as follows.

add a new worksheet using vba in excel

To add a new Sheet4 before Sheet2, use the following vba code.

Sub insert_before()
Worksheets.Add Before:=Worksheets("Sheet2")
End Sub

In the Before argument, we specify the sheet before which we want to insert a new worksheet. When you run this, Sheet4 is added before Sheet2.

insert a new worksheet before a worksheet using vba

Similarly, you can add a new worksheet after a specific worksheet with the help of After argument.

Sub insert_after()
Worksheets.Add After:=Worksheets("Sheet2")
End Sub

A new Sheet5 adds after Sheet2.

add a new worksheet after a worksheet using vba

Delete a Worksheet using VBA

We can delete the active worksheet with the help of the Delete method of Worksheets collection.


For example, this Sub procedure would delete Sheet5.

Sub delete()
End Sub

A message for confirmation for Sheet5 Deletion appears on the screen when you run the Sub Procedure. Click on Delete and Sheet5 is deleted.

Delete a worksheet using vba

You can delete the active sheet by this code.


This brings us to end.

Thank you for reading.

Leave a Comment