[Excel VBA] 3 Ways to Select/Activate Sheets and How to Choose

When performing operations across multiple sheets in VBA, you first need to clearly specify “which sheet to operate on.” This is equivalent to a user manually clicking on a sheet tab.

In VBA, there are two main ways to select (activate) a sheet: using the Index Number (order from the left) or using the Sheet Name. In this article, I will explain each method, which one you should use, and the important difference between Worksheets and Sheets.

Method 1: Selecting by Index Number (Order from Left)

This method specifies the sheet based on its position from the left in the sheet tabs. The leftmost sheet is 1, the next is 2, and so on.

Code and Explanation

Sub SelectSheetByIndex()
    ' Select the 1st (leftmost) worksheet
    ThisWorkbook.Worksheets(1).Select
    
    ' Confirm the name of the selected sheet
    MsgBox "Selected the 1st worksheet: [" & ActiveSheet.Name & "]"
End Sub

• Worksheets(1): Refers to the first worksheet.
• .Select: Selects (activates) the specified sheet.

Note: If the user changes the order of the sheets by dragging and dropping, Worksheets(1) will refer to a different sheet. Because this creates a risk of operating on the wrong sheet, this method is generally not recommended.

Method 2: Selecting by Sheet Name (Recommended)

This method directly specifies the name displayed on the sheet tab. Since it is not affected by the order of the sheets, it is the safest and most reliable method.

Code and Explanation

Sub SelectSheetByName()
    Dim sheetToSelect As String
    sheetToSelect = "SalesData" ' Example: Specify the sheet name you want to control

    On Error Resume Next ' Avoid error if the sheet does not exist
    
    ' Select the worksheet with the specified name
    ThisWorkbook.Worksheets(sheetToSelect).Select
    
    ' Handle cases where an error occurred (e.g., wrong sheet name)
    If Err.Number <> 0 Then
        MsgBox "The sheet named [" & sheetToSelect & "] was not found.", vbCritical
    Else
        MsgBox "Selected the [" & sheetToSelect & "] sheet."
    End If
    
    On Error GoTo 0 ' Reset error handling
End Sub

• Worksheets("SalesData"): Accurately refers to the worksheet named “SalesData”.

With this method, even if the user rearranges the sheets, the macro will always select the correct sheet.

Difference Between Worksheets and Sheets

In VBA, there are two types of objects that refer to a collection of sheets: Worksheets and Sheets.

• Worksheets: Contains only worksheets (standard sheets with cells).
• Sheets: Contains all types of sheets (worksheets, chart sheets, old macro sheets, etc.).

Usually, you will use Worksheets because you are mostly manipulating cells. However, if you want to include non-worksheet types, such as activating a Chart Sheet, you must use Sheets.

Sub SelectAnySheet()
    ' Select the 3rd "Sheet" in the workbook (regardless of type)
    ThisWorkbook.Sheets(3).Select
    
    ' Check the type of the selected sheet
    MsgBox "Selected the 3rd sheet: [" & ActiveSheet.Name & "]" & vbCrLf & _
           "The type of this sheet is: " & TypeName(ActiveSheet)
End Sub

Summary

There are several ways to select a sheet in VBA, but it is best to use them according to the following guidelines:

Conclusion: To ensure the stability of your macros, make it a rule to always select sheets by their Sheet Name.