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.

VBA Worksheet - Sheets.jpg

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

VBA Worksheet - Worksheets.jpg
Key Takeaways
  • A VBA Worksheet is an object that can be used to reference worksheets and play around with properties, methods, and objects.
  • We can activate any worksheets by index number or using the worksheet name.
  • We can give the name to the worksheet in the Visual Basic editor to avoid users changing the worksheet’s name.
  • VBA worksheets will add new worksheets. Similarly, we can delete and rename worksheets.

Syntax of VBA Worksheets

Following is the syntax of the VBA worksheets object.

worksheets syntax

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


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.

VBA Worksheet - Use - Example.jpg

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

VBA Worksheet - Use - Step 1

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

Use - Step 2

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

Use - Step 3.jpg

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

Use - Step 4

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

Sub VBA_Worksheets_Ex1()
End Sub

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

Use - 4th worksheet

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

VBA Worksheet - Use - Sheet2.jpg

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.

Use - swap cost.jpg

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.

VBA Worksheet - Use - swap cost.jpg

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()
End Sub

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

VBA Worksheet - Use - select sheet2

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.

VBA Worksheet - Example 1.jpg

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

Example 1 - Step 1.jpg

Enter the desired worksheet name in double quotes.

VBA Worksheet - Example 1 - Step 2.jpg

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.

VBA Worksheet - Example 1 - Step 3

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.

VBA Worksheet - Example 1 - Step 4

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

VBA Worksheet - Example 1 - Step 5

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.

VBA Worksheet - Example 1 - Step 6

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.

VBA Worksheet - Example 1 - Step 7

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

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

VBA Worksheet - Example 1 - Output

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”)
End Sub

This code will select the worksheet named “Sales.”

VBA Worksheets - Example 2

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.

VBA Worksheets - Example 2 - Sales Report

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

VBA Worksheets - Example 2 - Subscript

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.

Example 2 - Step 1

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.

Example 2 - Step 2

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

VBA Worksheets - Example 2 - Step 3

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

VBA Worksheets - Example 2 - Step 4

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.

VBA Worksheets - Example 3

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.

VBA Worksheets - Example 3 - output

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.

Example 3 - chart1

Including the chart sheet, we have 5 in total.

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

Example 3 - chart1 -

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.

VBA Worksheets - Example 3 - Unhide

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: We can use this method to add new worksheets. The following is the syntax of the worksheet method.

VBA Worksheets - Example 4 -  add
  • 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 - Example 4 -  output

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.


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.


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.

Example 4 -  Copy

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


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


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)

1. How to select multiple worksheets in Excel VBA?

We must use the Array function inside the VBA Worksheet object to select multiple worksheets in Excel VBA.

The following code will select worksheets, “Sheet1”, “Sheet2”, and “Sheet3”.

Worksheets(Array(“Chart1”, “Sheet1”, “Intro”, “Sales”)).Select

2. How To Switch Between Worksheets in Excel VBA?

We need to specify the worksheet name and choose the activate method to switch between worksheets.

3. What Is the Difference Between Sheets And Worksheets In VBA?

Sheets: In Excel, we have worksheets and chart sheets. As part of VBA Sheets, all these sheets are considered as VBA Sheets.

Sheets = Worksheets + Sheets

Worksheets: VBA Worksheets references only worksheet and do not consider chart worksheets as VBA worksheets.

Worksheets = All Worksheets – Chart Sheets

4. How To Unhide All Worksheets VBA?

To unhide all worksheets, we need to use FOR EACH loop, which will go through all the worksheets and unhide them.

The following is the code to unhide all worksheets in one go.

Sub VBA_Worksheet_Unhide()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws
End Sub

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *