Select Worksheets

Maintained on

How to Select a Sheet

First, let’s start with the basic method for selecting a specific sheet. In Excel VBA, you use the Worksheets or Sheets object to manipulate sheets.

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

This code selects the sheet named “Sheet1”.
As a special case, when the sheet name is only numbers, handle it as follows:

Select_sheet_by_specifying_index
Sub SelectSingleSheet()
    Worksheets(1).Select
End Sub
Difference between Worksheets and Sheets
Worksheets targets only worksheets.
Sheets is a collection that includes both worksheets and chart sheets.

How to Select Multiple Sheets

To select multiple sheets, use the Array function to specify the sheet names you want to select as an array.

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

This code selects two sheets, “Sheet1” and “Sheet2”, simultaneously.

On-screen behavior

When selecting multiple sheets, the first selected sheet is treated as the “active sheet”.

Advanced Code Examples

Dynamically Selecting Sheets

When sheet names change dynamically, you can select sheets using variables.

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

This code selects the sheet using the sheet name specified in the sheetName variable.

Selecting Sheets Based on Specific Conditions

For example, you can select sheets when the sheet name matches a specific pattern.

Sub SelectSheetsByPattern()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If InStr(ws.Name, "Report") > 0 Then
            ws.Select False '_Passing_False_allows_selecting_multiple_sheets
        End If
    Next ws
End Sub

This code selects all sheets whose names contain the string “Report”.

Info

Using Select False allows you to add new sheets to the current selection. This enables selecting multiple sheets.

Important Considerations

Cautions

Before selecting and manipulating a sheet, always ensure that the sheet exists. Attempting to select a non-existent sheet will cause a runtime error. - When multiple sheets are selected, some operations (e.g., cell formatting) may be restricted. - Sheet selection simulates Excel UI operations, which may increase execution time. Whenever possible, consider manipulating sheets or cells directly without selecting them.

Summary

Using VBA, you can programmatically select single or multiple sheets. This makes Excel work more efficient and reduces manual work. However, in some cases, it’s more efficient to operate without selecting sheets, so choose the optimal method.

By mastering these techniques, you’ll be able to make even better use of Excel.

#VBA #Excel #Sheet #Selection
Previous Page
How to Retrieve Values from a Specific Range in a Worksheet Using VBA