Select Worksheets

Selecting a Single Sheet

Pass a sheet name or index number to the Worksheets collection and call the Select method.

Public Sub SelectSingleSheet()
    Worksheets("Sheet1").Select
End Sub

Public Sub SelectSingleSheetByIndex()
    Worksheets(1).Select
End Sub

The index is the position in the workbook (hidden sheets are counted). Note that the string "1" means “the sheet named 1”, while the number 1 means “the first sheet”.

Select requires the sheet to be visible. Trying to select a hidden sheet raises run-time error 1004.

Selecting Multiple Sheets

Pass the sheet names as an array with the Array function to select several sheets at once.

Public Sub SelectMultipleSheets()
    Worksheets(Array("Sheet1", "Sheet2")).Select
End Sub

Examples

Selecting a Sheet via a Variable

Public Sub SelectSheetDynamically()
    Dim sheetName As String
    sheetName = "Sheet3"
    Worksheets(sheetName).Select
End Sub

Selecting All Sheets Whose Name Matches a Condition

Setting the Replace argument of Select to False adds a sheet without clearing the current selection. The key is to select the first sheet with True (the default) and add the rest with False.

Public Sub SelectSheetsByPattern()
    Dim ws As Worksheet
    Dim isFirst As Boolean
    isFirst = True

    ThisWorkbook.Activate
    For Each ws In ThisWorkbook.Worksheets
        If InStr(ws.Name, "Report") > 0 And ws.Visible = xlSheetVisible Then
            ws.Select isFirst ' the first sheet replaces the selection; the rest are added
            isFirst = False
        End If
    Next ws
End Sub

If you pass False from the start, the sheet that is already active stays in the selection, so unintended sheets become targets.

Working Without Selecting

In most cases you do not need to select a sheet. Selecting switches the screen, which slows processing down and can cause bugs if another sheet is active while the code runs.

Public Sub WriteWithoutSelect()
    Worksheets("Sheet1").Range("A1").Value = "Hello"
End Sub

Summary

• Select one sheet with Worksheets("Name").Select or Worksheets(Number).Select
• Select several with Worksheets(Array(...)).Select
• When adding by condition, replace the selection with the first sheet and add the rest with Select False
• Where possible, skip selecting and refer to the sheet directly