Excel VBA Find Sheet by Name: Safe Code and Examples

To find a worksheet by its tab name in Excel VBA, use ThisWorkbook.Worksheets("Summary") and handle the error that occurs if the name is missing. For a safer reusable approach, loop through the workbook’s worksheets and return the matching object only when it exists.

Find a worksheet by name with a direct lookup

The shortest lookup is the Worksheets collection followed by the sheet’s name in parentheses. Assign the result to a variable declared as a Worksheet:

Sub FindSummarySheet()
    Dim ws As Worksheet

    ' Look for the tab named Summary in the workbook containing this macro.
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Summary")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "The Summary worksheet was not found."
    Else
        MsgBox "Found: " & ws.Name
    End If
End Sub

If the worksheet exists, ws refers to it, so you can use properties and methods such as ws.Range("A1").Value. If it does not exist, VBA raises an error when it evaluates the lookup. The brief On Error Resume Next allows the assignment to fail without stopping the macro; the following On Error GoTo 0 immediately turns normal error handling back on. The Nothing check then distinguishes success from failure.

Keep the error-suppression block as narrow as possible. Leaving On Error Resume Next active for the rest of a procedure can hide unrelated problems, making a macro appear to work while skipping failed operations.

Choose the correct workbook reference

A sheet name is only meaningful within a particular workbook. The workbook qualifier before .Worksheets determines where VBA searches, and choosing the wrong one is a common reason a lookup fails.

ThisWorkbook means the workbook that contains the running VBA code. It is usually the right choice for a macro stored in the workbook it manages.
ActiveWorkbook means the workbook currently active in Excel. It can be appropriate for a general-purpose macro that should act on the user’s selected workbook, but it may change as the macro opens or activates files.
A variable such as wb lets a procedure search a workbook selected earlier in the macro. This is useful when working with multiple open files.

For example, if a macro is stored in Personal.xlsb but should work on the workbook currently in front of the user, ThisWorkbook would point to Personal.xlsb, not the visible data file. Conversely, using ActiveWorkbook in a macro that opens another workbook can make later lookups target the wrong file. Decide which workbook the task concerns, then qualify every lookup consistently.

Use a reusable function that returns the worksheet

A small function can search without suppressing errors. It returns the worksheet object if a match is found and returns Nothing if the workbook does not contain that tab. Passing the workbook explicitly also makes the function useful for more than one file.

Function FindWorksheet(ByVal sheetName As String, ByVal wb As Workbook) As Worksheet
    Dim candidate As Worksheet

    ' Compare worksheet names without regard to letter case.
    For Each candidate In wb.Worksheets
        If StrComp(candidate.Name, sheetName, vbTextCompare) = 0 Then
            Set FindWorksheet = candidate
            Exit Function
        End If
    Next candidate

    ' If no match was found, the function result remains Nothing.
End Function

Call the function from another procedure and check its result before using it:

Sub UseFoundWorksheet()
    Dim ws As Worksheet

    Set ws = FindWorksheet("Summary", ThisWorkbook)

    If ws Is Nothing Then
        MsgBox "Add or rename the worksheet before continuing."
        Exit Sub
    End If

    ws.Range("A1").Value = "Updated"
End Sub

In the comparison, vbTextCompare makes the test case-insensitive. Excel does not allow two worksheet tabs in the same workbook to differ only by letter case, so a case-insensitive comparison matches how names are normally treated. If you want to make the comparison rule explicit for a particular task, choose the appropriate StrComp comparison mode; for sheet names, a binary comparison is usually unnecessary.

Worksheets versus Sheets

Use Worksheets when the code specifically expects a grid-based worksheet. A workbook’s Sheets collection can also include chart sheets, which are separate sheets containing a chart rather than the usual cell grid. A variable declared As Worksheet cannot hold a chart sheet.

If your task intentionally needs any kind of sheet, you can search Sheets and declare the result as Object, or inspect each item’s type before using it. For most data, formulas, and cell-formatting work, Worksheets is clearer and prevents chart sheets from being treated as though they have worksheet cells.

A hidden or very hidden worksheet is still part of the workbook’s Worksheets collection and can be found by name. Visibility affects whether a user can see the tab, not whether VBA can reference the worksheet. You may still need to change its Visible property before a user can view it.

Search for a name before creating a sheet

A frequent related task is to create a worksheet only if it does not already exist. First perform the lookup, then add a sheet only when the result is Nothing. This avoids accidentally creating duplicate tabs or raising an error by trying to add a name that is already in use.

Sub GetOrCreateReportSheet()
    Dim ws As Worksheet

    Set ws = FindWorksheet("Report", ThisWorkbook)

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "Report"
    End If
End Sub

The function shown earlier must be in a standard module or another module where this procedure can access it. The new sheet is created with Excel’s default name and then renamed. A name may fail to assign if it is invalid or already taken; Excel worksheet names cannot be blank, cannot exceed 31 characters, and cannot contain certain characters such as a colon or a slash. If the requested name comes from user input, validate it and handle a possible rename error rather than assuming the assignment will always succeed.

Common lookup problems and how to fix them

The tab name is not an exact match. Check spelling, punctuation, and spaces. A trailing space in a cell or input string can make a name appear correct while still failing to match. Compare the requested name with the visible tab label or inspect ws.Name in the Immediate window.
The code searches the wrong workbook. Replace an unintended ActiveWorkbook or ThisWorkbook reference with the workbook object the task actually uses.
The code expects a worksheet but the tab is a chart sheet. Use Worksheets only for worksheets, or deliberately account for chart sheets through Sheets.
The code uses a sheet’s position instead of its name. Worksheets(1) means the first worksheet in collection order, not a worksheet called “1.” Prefer the tab name when the macro depends on a particular sheet.
Error handling hides a different failure. Reset error handling with On Error GoTo 0 immediately after a narrowly scoped lookup, or use the loop-based function to avoid suppressing lookup errors.

Worksheet tab names are not the same as VBA code names. The tab name is the text displayed to the user and is available through ws.Name. A worksheet’s code name appears in the Visual Basic Editor’s Project Explorer and can be used directly in VBA, but it is a different identifier. Code names can be useful in a workbook that you control, while a name-based lookup is more appropriate when the tab label is configurable or supplied by a user.

When to use a direct lookup or a loop

For one lookup in a short procedure, a direct reference with a carefully handled missing-sheet case is concise. The loop-based function is better when the same search is used in several places, when you want explicit control over comparison, or when you prefer not to use error suppression.

For a workbook with a modest number of tabs, searching through each worksheet is straightforward and easy to maintain. If a macro repeatedly searches a very large workbook inside a tight loop, find the worksheet once and keep its object reference rather than repeating the lookup for every row. The main benefit is simpler, clearer code; a sheet-name lookup is generally not a performance bottleneck in ordinary workbook automation.

There is no need to use a cell formula such as INDIRECT to find a sheet for VBA code. A formula returns a cell value, whereas the VBA lookup returns a worksheet object that can be used to access ranges, properties, and methods. Keep the two approaches separate: use formulas for worksheet calculations and VBA object references for macro operations.

Run and protect the VBA macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, the shortcut is commonly Option+F11; some keyboards require holding Fn as well. You can also open the editor from the Developer tab by selecting Visual Basic. If Developer is not visible, enable the Developer tab in Excel’s ribbon settings. Excel for the web can open macro-enabled files but cannot run or edit VBA macros; use the desktop app.

To try the examples, insert a standard module in the Visual Basic Editor, paste in the procedure and any function it calls, and run the desired macro from the editor. A workbook must be saved in a macro-enabled format such as .xlsm if it needs to retain its VBA project. Only enable macros in files you trust.

Before running a macro that changes a workbook, save a copy. VBA actions cannot be undone with Ctrl+Z, and a macro that writes to cells, adds sheets, or changes workbook structure may not be reversible through Excel’s Undo command.

Frequently Asked Questions

How do I check whether a sheet exists in Excel VBA?
Try a narrowly scoped lookup with error handling and then check whether the worksheet variable is Nothing, or loop through Worksheets and compare each tab’s Name. The function example above returns Nothing when no match is found.

Is the VBA sheet-name lookup case-sensitive?
Excel sheet names cannot be duplicated just by changing capitalization, and typical name lookups are case-insensitive. The reusable function makes that behavior explicit with StrComp and vbTextCompare.

What is the difference between ThisWorkbook and ActiveWorkbook?
ThisWorkbook is the file containing the VBA code. ActiveWorkbook is the file currently active in Excel, which may be a different workbook. Use the one that matches the workbook your macro is supposed to search.

Can VBA find a hidden worksheet by name?
Yes. A hidden or very hidden worksheet remains in the Worksheets collection, so VBA can return its object by name. It does not have to be made visible just to read or change its cells.

Can I use this code in Excel for the web?
No. Excel for the web does not run VBA macros. Use the desktop version of Excel for Windows or Mac to open the Visual Basic Editor and run 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.