VBA Worksheets

What is Excel VBA Worksheets?

VBA Worksheet is an object that is part of the VBA Workbook. VBA Worksheet is an object that helps us select any specific worksheet; we can insert a new sheet, reference any worksheets and Worksheets.

Sheets: In Excel, we have worksheets and chart sheets.

Worksheets: VBA Worksheets reference only worksheets and do not consider chart worksheets.

Syntax of VBA Worksheets

Following is the syntax of the VBA worksheets object.

We can give the worksheet name in double quotes or its index number in the syntax. For example, the following code will activate the 1^st worksheet of the workbook.

Worksheets(1).Activate

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use Worksheets Objects in VBA?

Let us show you a step-by-step approach to using the VBA worksheet object.

Before we start the steps, we have the following worksheets in our Excel workbook. We will show you different ways of using the VBA worksheets object.

Step 1: Start the sub-procedure by naming the macro.

Step 2: Inside the sub-procedure, enter the Worksheets object name, and we can see that in the IntelliSense list.

Step 3: Open the parenthesis for the Worksheet object, and you can see the syntax for the VBA Worksheet.

Step 4: Assume we must activate the worksheet “Sheet2.” Then, we can pass the index number 2 because the worksheet “Sheet2” is in the 2^nd list.

Step 5: Close the parenthesis enter dot and choose the “Activate” method of the worksheet.

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

Before we run this code, let us select the 4^th worksheet.

Now execute the code, which should activate the VBA Worksheet “Sheet2”.

However, the problem with using an index number to activate any worksheet is that whenever their position in the worksheet list is changed, the VBA code with the index number will no longer choose the “Sheet2” worksheet; instead, it will choose whichever is in the second position.

For example, let us swap the position of the 2nd worksheet and the 5th worksheet.

Now, we have the “Cost” worksheet in second place and the “Sheet2” worksheet in fifth place. Let’s run the code, and it will activate the following worksheet.

This time, it activates the “Cost” worksheet because while executing the code, the “Cost” worksheet is in the second place. Hence, using index numbers to select any worksheets would lead to error-prone work.

Instead, we can use the worksheet name to activate any worksheets.

Sub VBA_Worksheets_Ex1()
Worksheets(“Sheet2”).Activate
End Sub

Now, if we run this code, it will always activate the worksheet “Sheet2,” irrespective of the position in the workbook.

Examples of Excel VBA Worksheets

Let us show you various examples of using the worksheets object in the practical world.

Example #1: Select Worksheets by Name

Referring to the worksheet by their name is the most accurate way of selecting any worksheet. For example, look at the following worksheets in an Excel workbook.

We have five different worksheets. Let’s write a code to select the worksheet “Sales.” Inside the sub-procedure, enter the VBA worksheets object.

Enter the desired worksheet name in double quotes.

Close the bracket and enter to access all the properties, objects, and methods associated with the VBA worksheets object.

The problem with accessing the worksheet directly using the worksheet object is we don’t get to see the properties, objects, and methods associated with the VBA worksheets object.

Hence, we will use the variables to set worksheet reference to a variable and then use the variable going forward instead of the long worksheet name. Define a variable using the DIM keyword.

Once the variable is defined, we must assign a VBA data type. Let’s assign a Worksheet variable since we will work with a VBA worksheet object.

Next, use the “Set” keyword and assign the worksheet reference to this variable.

The variable WS holds the reference of the worksheet “Sales.” Hence, we can use the variable name instead of the full worksheet name. Enter the worksheet variable name and enter a dot.

As we can see, the IntelliSense list shows all the properties, objects, and methods associated with the VBA worksheet object.

From the IntelliSense list, choose the “Activate” method.

Sub VBA_Worksheets()
Dim Ws As Worksheet
Set Ws = Worksheets(“Sales”)
Ws.Activate
End Sub

Let’s execute this code, and it should activate the worksheet “Sales”.

Example #2: Problem with Worksheet Name

One of the issues of dealing with worksheet names to activate worksheets is if somebody changes the name of the worksheet, then the written code no longer functions, leading to a subscript out-of-range error. For example, let’s take the same code from the previous example.

Sub VBA_Worksheets()
Dim Ws As Worksheet
Set Ws = Worksheets(“Sales”)
Ws.Activate
End Sub

This code will select the worksheet named “Sales.”

Assume you sent the file to someone, and they need to be made aware of the automation of the work through VBA, and they change the worksheet name to something else.

The worksheet name has been changed from “Sales” to “Sales Report.” Let’s execute the code and see what happens.

We have the “Run Time Error ‘9’ Subscript out of range.”

In the code, we have given the worksheet name “Sales,” but the worksheet name has been changed to “Sales Report.” Hence, the code didn’t recognize the worksheet in the Excel workbook. To deal with such scenarios, we can use special techniques. Let’s follow the steps listed below.

Step 1: Press ALT + F11, which will take us to the Visual Basic Editor window.

Step 2: On the left side, the “Microsoft Excel Objects” section contains all the worksheets this workbook has.

Choose the worksheet name “Sales” and press the F4 key. It will open the properties window.

Step 3: Choose the (Name) property and give the name as “Sales.”

Step 4: Now, in the code, instead of using the VBA worksheets object name and the worksheet name, use the name “Sales.”

If the user changes the worksheet name from “Sales,” it will not impact the code’s functionality. It will still activate the worksheet “Sales” only.

Example #3: Get the Count of Total Sheets in the Workbook

We can use the VBA worksheet object to get the count of the total number of worksheets in the given workbook.

Let’s look at the following worksheets image.

In total we have 6 worksheets. We can use the following code to get the total count of these worksheets.

Sub VBA_Worksheet_Count()
‘Define a variable to assign worksheets count
Dim WS_Count As Long
‘Get the worksheet count and assign to variable
WS_Count = Worksheets.Count
‘Show the count in a message box
MsgBox WS_Count
End Sub

Execute the code.

It is interesting, as we have six worksheets, but we got the count as 5.

We have a chart sheet, and the VBA Worksheet will not consider this. Hence, it excludes that from the count and retrieves only 5.

Now look at the following image.

Including the chart sheet, we have 5 in total.

Let’s execute the code now and see what happens.

Interesting! If we exclude the chart sheet, we should get the worksheets count as 4. Instead, we got the count as 5.

The VBA worksheet object counts the hidden worksheets whenever we use the Count method.

The worksheet “Sheet1” is hidden hence we have got the count as 5 not as 4.

Example #4: Methods using Worksheet Object

Let’s explore some of the common methods to be used in worksheet objects, and the following are some of the commonly used methods in worksheets.

• VBA Worksheets Add
• VBA Worksheets Delete
• VBA Worksheets Copy

VBA Worksheets Add: We can use this method to add new worksheets. The following is the syntax of the worksheet method.

• Before: We can choose before which worksheet we need to add the new worksheet.
• After: We can choose after which worksheet we need to add the new worksheet.
• Count: How many worksheets we must add.

Like this we can make use of these syntax expressions and add a new sheet.

For example, the following code will add two worksheets after the worksheet “Intro.”

Sub VBA_Worksheet_Methods()
Worksheets.Add After:=Worksheets(“Intro”), Count:=2
End Sub

Let’s execute this code, and we will get two new worksheets after the worksheet “Intro.”

VBA Worksheets Delete: We can use this method to delete any worksheets. The syntax of this method is below.

Worksheets(“Name of the worksheet”).Delete

We need to enter the worksheet name, which is to be deleted from the workbook.

Worksheets(“Sheet1”).Delete

It will delete the worksheet “Sheet1”.

If we do not provide any of the worksheet’s names and use the delete method, it will

delete all the worksheets.

Worksheets.Delete

It will delete all the worksheets except chart sheets.

VBA Worksheets Copy: We can copy the entire worksheet to a new workbook by using the copy method of the worksheet. The following is the syntax of the VBA Worksheets Copy method.

Like the Add method, we can choose before and after worksheets. The following code will copy all the worksheets to the new workbook.

Worksheets.Copy

The following code will copy only the “Sales” worksheet to the new workbook.

Worksheets(“Sales”).Copy

Important Things to Note

• VBA Worksheet object will show run time error ‘9’ Subscript Out of Range error if the given worksheet name is incorrect or not recognized by VBA.
• Since the worksheet is an object, we need to use the “Set” keyword to set the worksheet reference to any of the variables.
• Worksheets.Delete method will result in deleting all the worksheets of the active workbook. Hence, be specific in deleting any of the worksheets.
• VBA worksheets will not consider chart sheets as worksheets.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Worksheet, with its syntax and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Worksheets. We learn how to use Worksheet object in excel VBA with its syntax, examples & errors. You can learn more from the following articles –

• VBA Worksheet Function
• VBA LCASE FUNCTION
• VBA Random Number