Activate a sheet using VBA in Excel

Applying the Activate method on a Worksheet object makes it the active sheet, the sheet on top.

Select sheet

To be able to activate a sheet you first have to select it. Activating an object is simple, call the Activate method on the object. You will have to specify the object you want activated and that is where you have quite a few options which are discussed here.

Note: You can use the method Select in place of Activate and get the same result: the sheet being activated. However, Select has been designed for another purpose, namely to support the selection of one or more sheets and for that reason use of Activate is preferred to make a sheet the active one.

Activate sheet by number

It is called like this:


ActiveWorkbook.Sheets(Index:=1).Activate

The above code activates the first sheet in the specified workbook.

Note: To keep the sample code short we use the ActiveWorkbook method to provide the parent Workbook object whose Worksheet is being used. In real code using explicitly declared and assigned object variables is preferred, in the current case: Dim wbActiveWorkbook As Workbook: Set wbActiveWorkbook = ActiveWorkbook , quite likely with another object assignment expression.

The Worksheet object here is obtained from the Workbook Sheets procedure .

Note: In case there are more than one properties and/or methods to be called from looped items in the collection they are often placed in a With statement block. Using the With syntax makes the code structure clearer and has performance benefits.

Calling this method is equivalent to choosing the sheet's tab.

Activate sheet by name

Activate sheet with variable name

The most common way to refer to a sheet is by its name as displayed in the sheets tab:


ActiveWorkbook.Sheets(Index:="data2-17-14").Activate

Error Having the wrong name, referring to a sheet which does not exists, will cause Run-time error '9' : Subscript out of range.

If your macro will be used by others, you will want to add proper error handling, possibly like:


Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Sheets(Index:="data2-17-14")
On Error GoTo 0
If Not ws Is Nothing Then
    ws.Activate
Else
    
End If

Activate sheet by codename

You can also directly refer to a sheet using its CodeName property. This has the advantage that you can be sure the names sheet exists. , but this can only be done if the macro is in the same workbook as the sheet - which is often not the case.


Sheet1.Activate

Note: The CodeName property can only be changed in the VBA Editor class module property window. It can't be changed using VBA code.

Activate sheet based on cell value

An application for wanting to activate a sheet based on a cell value is where you have so many tabs that it becomes difficult to navigate. A possible solution would be to have a navigation worksheet listing the target sheets and having the sheet open that corresponds to the selected cell.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strWorksheet As String: strWorksheet = Target.Value
	On Error Resume Next
    Worksheets(Index:=strWorksheet).Activate
End Sub

Note: If you are wondering, to add the above code correctly, you have to:

in the Project Explorer, doubleclick the appropriate Sheet class module to have it opened, and
In the module left combo box (General), Select Worksheet.
If not appearing automatically as default, the appropriate event handler code can be selected from the right combo box.

Select sheet and cell

A cell can only be selected if the sheet on which it lives is the active one. Therefore, generally you will precede the selection by activating its Parent. For example, instead of Application.Range("Sheet2!B2").Select you will be safer:


With Application.Range("Sheet2!B2")
    .Parent.Activate
    .Select
End With

Activate sheet in another workbook

To activate a sheet the workbook itself need not be the active workbook. You only have to identify the workbook, and the worksheet you want to activate and then do it:


Dim wb As Workbook
Set wb = Workbooks(Index:="data123.xlsx")
Dim ws As Worksheet
Set ws = wb.Sheets(Index:="Countries")
ws.Activate

Note: If this is the only thing you want to do you can significantly sorten the code: Workbooks(Index:="data123.xlsx").Sheets(Index:="Countries").Activate. In all other cases you are better of introducing variables for the workbook and or worksheet as was done above.

Activate sheet without showing

You do not need to make a worksheet (or workbook!) active to be able to operate on it. The way to go is to declare a worksheet variable and instantiate it using a Set statement, as was done in the above section. Using variables this way instead of relying on the sheet being active is both faster and safer.

Author: Mark Uildriks

Reference: Worksheet.Activate() | Microsoft Learn