VBA Activate Sheet

Excel VBA Activate Sheet

While working in VBA we sometimes refer to another sheet or use another sheet’s properties, suppose we are in sheet 1 working but we want a value from cell A2 in sheet 2, if we refer to sheet 2’s value without activating the sheet first then we will not be able to access the value so to activate a sheet in VBA we use worksheet property as Worksheets(“Sheet2”).Activate.

In excel, we always work with worksheets. Worksheets have their own name to identify better. In regular spreadsheet workings, we directly navigate thorough shortcut keys, or we directly select the sheet by clicking on them. However, in VBA, it is not that easy; we need to specify the sheet name we are referring to, then we can use the “Select” method to select the sheet.

What is VBA Activate Method?

As the name says, it activates the specified worksheet. To activate the sheet, we need to mention the exact worksheet name by using worksheets object. For example, if you want to activate a sheet called “Sales,” then you can use the below code.

Worksheets(“Sales”).Activate

Syntax

So, the syntax of the Activate method is as follows.

Worksheet (“Name of the Sheet”).Activate

Here worksheet is the object, and activates are the method.

Example #1 – Activate Sheet by its Index Number

In excel, we work with multiple sets of worksheets, and oftentimes, we need to move between one sheet to another to get the job done. In VBA, we can use the Activate method to activate the particular excel sheet.

For example, I have created three sheets named “Sales 2015”, “Sales 2016”, and “Sales 2017”.

We can activate the sheets in two ways. One is by using sheet index numbers, and another one is by using the sheet name itself.

Now, if I want to select the 2^nd sheet, I will use the Worksheet object and mention the sheet index number as 2.

Code:

Sub Activate_Example1()

   Worksheets(2).Activate

End Sub

When you run the code using the F5 key or manually then, this will activate the second sheet, i.e., “Sales 2016”.

If I want to activate the 3^rd sheet, I will use 3 as the sheet index number.

Code:

Sub Activate_Example1()

    Worksheets(3).Activate

End Sub

This will activate the third sheet, i.e., “Sales 2017”.

Now I will interchange the 2^nd and third sheets.

Now technically, “Sales 2017” is my third sheet, and “Sales 2016 is my second sheet. Now I will use the sheet index number as 3 and see what happens.

Code:

Sub Activate_Example1()

    Worksheets(3).Activate

End Sub

In my view, it has to select the “Sales 2017” sheet, but it will select the “Sales 2016” sheet because in the order, “Sales 2016” is the third sheet.

So, it is always a safe option to activate the sheet by its name.

Example #2 – Activate Sheet by its Name

Now we will see how to activate sheets by its name. In the place of a sheet index number, we need to mention the sheet name in double-quotes.

Code:

Sub Activate_Example2()

    Worksheets("Sales 2016").Activate

End Sub

When you run the code manually or using shortcut key F5 then, this would activate the sheet “Sales 2016” irrespective of the position in the workbook.

Not only the Worksheets object, but we can also use the “Sheets” object to activate the sheet.

Below is the code.

Code:

Sub Activate_Example2()

    Sheets("Sales 2016").Activate

End Sub

Worksheets can access only Worksheets Object and cannot access “Chart” sheets. If you use the Sheets object, we can access all the sheets in the workbook.

Example #3 – Activate Sheet from Another Workbook

Like how we need to mention the sheet name to activate the particular sheet, similar in case of activating the sheet from another workbook requires the “Workbook” name also.

Code:

Sub Activate_Example3()

    Workbooks("Sales File.xlsx").Sheets("Sales 2016").Activate

End Sub

This will activate the sheet “Sales 2016” from the workbook “Sales File.xlsx.”

Activate Sheet vs. Select Sheet Method

We can use methods to perform the same action, i.e., Activate and Select methods. There is a slight difference between these two methods.

#1 – Activate Method

By using the Activate method, we can only activate the specified worksheet.

For example, look at the below code.

Code:

Sub Activate_Example()

    Worksheets("Sales 2016").Activate

End Sub

As we know, this code will select the worksheet “Sales 2016”.

#2 – Select Method

By using the Select method, we can actually perform other tasks as well.

Now, look at the below code.

Code:

This code not only activates the sheet “Sales 2016” but also selects the range of cells from A1 to A10.

Recommended Articles

This has been a guide to VBA Activate Sheet. Here we learn how to use the activate method in VBA to activate a particular excel sheet along with practical examples and downloadable templates. Below you can find some useful excel VBA articles –

• VBA TimeValue
• VBA DatePart Examples
• Option Explicit in VBA
• VBA Remove Duplicates
• VBA StatusBar