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.

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.

Sheets=Worksheets+ChartSheets

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.

Worksheets(<index>).<property_name>
or
Worksheets(<index>).<method_name>()

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

Sub activate_sheet2()
Worksheets(2).Activate
End Sub

Put this Sub Procedure in the VBE and run it.

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.

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

Sub activate_Jan()
Worksheets("Jan").Activate
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.

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

Sub activate_Jan()
January.Activate
End Sub

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

Adding a Worksheet using VBA

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

Worksheets.Add

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.

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.

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.

Delete a Worksheet using VBA

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

Worksheets("<Sheet_Name>").Delete

For example, this Sub procedure would delete Sheet5.

Sub delete()
Worksheets("Sheet5").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.

You can delete the active sheet by this code.

ActiveSheet.Delete

This brings us to end.

Thank you for reading.