Excel VBA: Open a Worksheet by Name or Index

To open a worksheet in Excel VBA, activate it with a statement such as ThisWorkbook.Worksheets("Data").Activate; replace Data with the sheet’s tab name. This brings the worksheet to the front, but it does not open a separate workbook file.

What “open a worksheet” means in Excel VBA

Excel uses “open” for a few different actions. A workbook is the file, such as an .xlsx or .xlsm file, while a worksheet is one tab inside that workbook. VBA can open a workbook from a file path, then activate one of its worksheets; if the workbook is already open, the usual request to “open a worksheet” means making that sheet the active view.

There are two related but different operations:

Set ws = ThisWorkbook.Worksheets("Data") assigns a worksheet object to a variable. It does not change what the user sees.
ThisWorkbook.Worksheets("Data").Activate makes the worksheet active and displays it in the Excel window.

That distinction matters in longer macros. A procedure can read or write to a worksheet through an object variable without navigating to it. Activate a sheet only when changing the visible sheet is part of the task, such as presenting a report to a user.

Activate a worksheet by its tab name

For a known sheet, use its name in the workbook’s Worksheets collection. The name must match the worksheet tab exactly, including spaces and punctuation. For example, a sheet named Monthly Report is addressed as Worksheets("Monthly Report").

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, enter a procedure, and run it with F5 while the cursor is inside the procedure. Alternatively, return to Excel and use Alt+F8 to choose a macro. If the Developer tab is not shown, enable it under File > Options > Customize Ribbon by checking Developer.

Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the practical way to recover from an unwanted change. This example checks for the requested sheet and reports an understandable message if it is missing:

Sub OpenDataWorksheet()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim candidate As Worksheet

    ' ThisWorkbook is the workbook containing this macro.
    Set wb = ThisWorkbook

    ' Find the sheet without raising an error if its name is absent.
    For Each candidate In wb.Worksheets
        If candidate.Name = "Data" Then
            Set ws = candidate
            Exit For
        End If
    Next candidate

    If ws Is Nothing Then
        MsgBox "The Data worksheet was not found.", vbExclamation
        Exit Sub
    End If

    ' Make a hidden sheet visible before activating it.
    On Error GoTo SheetError
    ws.Visible = xlSheetVisible
    ws.Activate
    Exit Sub

SheetError:
    MsgBox "Excel could not show the worksheet. Check whether the workbook structure is protected.", vbExclamation
End Sub

The visibility assignment in this example lets it handle a hidden worksheet as well as a visible one. If the workbook’s structure is protected, Excel may prevent a hidden sheet from being made visible; the error handler displays a message rather than leaving the macro to stop with an unhandled error.

Use a worksheet’s position instead

VBA can also identify a worksheet by its position in the Worksheets collection. For example, ThisWorkbook.Worksheets(1).Activate activates the first worksheet in that collection. This is short, but it can be fragile: inserting, deleting, or rearranging worksheets can change which sheet occupies a given position.

Use an index only when position is meaningful and controlled, such as a macro that deliberately processes every worksheet in order. For a sheet that has a stable purpose, a descriptive tab name is generally clearer and safer. Also note that Worksheets(1) means the first worksheet, not necessarily the first tab of any type. The broader Sheets collection can include chart sheets as well as worksheets, so its index may refer to a different tab when a workbook contains charts.

Choose the correct workbook reference

A frequent source of VBA mistakes is naming the right sheet in the wrong workbook. ThisWorkbook means the workbook that contains the running VBA code. ActiveWorkbook means the workbook currently active in Excel, which may be a different file. If a macro is stored in the workbook it controls, ThisWorkbook is often the reliable choice.

For a macro stored in the Personal Macro Workbook or an add-in, ThisWorkbook points to that container rather than the user’s data file. In that situation, use a known workbook variable or deliberately use ActiveWorkbook after confirming which file is active. Avoid relying on an unqualified reference such as Worksheets("Data").Activate: it can depend on the active workbook and is less explicit.

Here is the basic pattern for making the active workbook’s Data sheet visible and active. It assumes the intended file is already the active workbook:

Sub OpenDataInActiveWorkbook()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Use the workbook currently selected in Excel.
    Set wb = ActiveWorkbook

    On Error GoTo SheetError
    Set ws = wb.Worksheets("Data")
    ws.Visible = xlSheetVisible
    ws.Activate
    Exit Sub

SheetError:
    MsgBox "The Data sheet could not be opened in the active workbook.", vbExclamation
End Sub

Because the error handler covers both a missing sheet and a problem showing it, the message is intentionally general. If the macro must distinguish those cases, check the worksheet names first, as in the earlier example, and then handle visibility separately.

Show a hidden worksheet before activating it

A normal hidden worksheet does not appear as a tab, so trying to activate it directly can fail. Set its Visible property to xlSheetVisible first. Worksheets can also be marked xlSheetVeryHidden; that setting prevents users from unhiding them through the standard Unhide dialog, but VBA can still change the property when the workbook permits it.

If Excel reports that the property cannot be changed, check whether the workbook structure is protected. Protecting workbook structure can block actions such as adding, deleting, moving, or unhiding sheets. The appropriate response is to use the authorized password or ask the workbook owner; a macro should not be used to bypass protection. Once a sheet is visible, Activate makes it the current sheet.

Activate, select, and work without changing the view

Activate is usually the straightforward choice when the goal is to display one worksheet. Select can also select a sheet, but it is more commonly useful when selecting multiple sheets together or when existing code specifically expects a selection. Selecting several sheets can group them: edits made while they are grouped may affect all selected sheets, so ungroup them before continuing with ordinary work.

Many macros do not need either method. For example, this assigns a worksheet to an object variable without switching the visible tab: Set ws = ThisWorkbook.Worksheets("Data"). The procedure can then refer to ws.Range("A1") or other members directly. This is less dependent on whichever sheet happens to be active and can make code easier to follow. If the macro needs to show the result at the end, activate the destination sheet then, rather than navigating back and forth during each operation.

Open a workbook first, then activate its worksheet

If the file itself is not open, the macro needs to open the workbook before it can address a worksheet inside it. In this example, the file path is illustrative and must be changed to a valid path on the computer. Opening files changes the Excel session, so save a copy of important workbooks before running file-handling macros.

Sub OpenReportFile()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Replace this example path with the actual workbook location.
    Set wb = Workbooks.Open("C:\Reports\MonthlyReport.xlsx")

    On Error GoTo SheetError
    Set ws = wb.Worksheets("Summary")
    ws.Activate
    Exit Sub

SheetError:
    MsgBox "The workbook opened, but the Summary sheet could not be activated.", vbExclamation
End Sub

This procedure assumes the workbook exists at that location and that it contains a worksheet named Summary. A wrong path causes the workbook-opening line to fail before the later error handler is reached. In production code, validate the path or add a separate error-handling section around Workbooks.Open. If the workbook is already open, avoid opening a second copy unnecessarily; identify the existing workbook and use its worksheet collection instead.

Common errors and practical checks

When an activation statement fails, check the workbook reference, exact sheet name, and visibility before changing the code. Sheet names may contain spaces, and a renamed tab makes an old hard-coded name invalid. A misspelled workbook variable or a reference to the wrong open file can produce a similar symptom.

“Subscript out of range”: The named sheet may not exist in the workbook being referenced, or an index may be outside the collection’s range.
The sheet is hidden: Set its Visible property to xlSheetVisible before activation, provided the workbook allows the change.
The workbook is protected: Workbook-structure protection can prevent visibility changes. Confirm permissions with the owner.
The code uses ActiveWorkbook unexpectedly: Another workbook may have become active. Qualify the worksheet through the intended workbook variable.
The procedure runs in Excel for the web: Excel for the web can preserve a workbook containing VBA, but it cannot run or edit VBA macros. Use desktop Excel for that work.

VBA is available in desktop Excel for Windows and Mac, though menus and keyboard behavior differ. On Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor in Excel; some keyboards require using the Fn key with function-key shortcuts. The Developer tab and macro dialogs may be reached differently depending on the Mac version and settings. The VBA object model is broadly similar, but file paths and some platform-specific features differ, so Windows paths such as C:\Reports\... are not portable Mac paths.

Macro safety and when not to use VBA

Only run macros from sources you trust. A macro can change or delete workbook data, open files, or perform other actions with the permissions of the Excel user. Do not enable all macros simply to make one workbook work. Review the file’s source and follow your organization’s security policy; Excel’s Trust Center settings are available through File > Options > Trust Center > Trust Center Settings on Windows.

For a one-time navigation task, clicking the worksheet tab may be simpler than writing code. For a repeatable process—such as a button that opens a designated report sheet, or a macro that activates a user-selected destination—VBA can save steps. If the file will be used in Excel for the web, a VBA-based navigation feature is not suitable there; use ordinary sheet tabs or a supported non-VBA solution instead.

Frequently Asked Questions

What is the VBA code to open a sheet named Sheet1?
Use ThisWorkbook.Worksheets("Sheet1").Activate when the macro is stored in the workbook containing that sheet. The name must match the tab exactly.

How do I activate a worksheet by its number?
Use a one-based index, such as ThisWorkbook.Worksheets(2).Activate, for the second worksheet. Prefer a name if sheet order may change.

Why does VBA say “Subscript out of range”?
Usually the specified sheet name or index is not present in the workbook object used by the code. Check the spelling, workbook reference, and whether you meant Worksheets or Sheets.

Can VBA open a hidden worksheet?
Yes, if workbook protection does not prevent it. Set the sheet’s Visible property to xlSheetVisible, then activate it.

Can Excel for the web run a VBA macro?
No. Excel for the web can keep a macro-enabled workbook, but you need desktop Excel to run or edit its VBA code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.