VBA Check If Worksheet Exists: Code and Examples

To check whether a worksheet exists in VBA, try to set a Worksheet variable from the target workbook’s Worksheets collection and test whether the variable is Nothing. For example, use Set ws = wb.Worksheets("Sales") inside a small error-handled function; this distinguishes a missing worksheet from one that is present, including a hidden sheet.

A simple worksheet-existence function

Excel raises an error when VBA asks a workbook’s Worksheets collection for a name that is not there. The common approach is to handle that expected lookup error briefly, then return whether the worksheet variable was set. Put this function in a standard VBA module:

Public Function WorksheetExists(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
    Dim ws As Worksheet

    ' A missing workbook or blank name cannot identify a worksheet.
    If wb Is Nothing Then Exit Function
    If Len(sheetName) = 0 Then Exit Function

    ' Suppress the missing-sheet lookup error only for this line.
    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0

    WorksheetExists = Not ws Is Nothing
End Function

The function returns True when the named worksheet is found and False otherwise. It takes both a name and a workbook deliberately: specifying the workbook makes the check predictable even if a user clicks into another file while a macro runs. The function does not change the workbook.

To add the function, open the Visual Basic Editor with Alt+F11 in Excel for Microsoft 365 on Windows. In the editor, choose Insert > Module and paste the code. On a Mac, Option+F11 commonly opens the editor; some keyboards require Fn+Option+F11. The editor’s menu names can vary slightly by Excel release.

Call the function before using a sheet

Pass the intended workbook explicitly, then put the sheet-specific work inside the successful branch. This example checks for a worksheet named Sales in the workbook that contains the macro:

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

    ' ThisWorkbook is the file containing this VBA code.
    Set wb = ThisWorkbook

    If WorksheetExists("Sales", wb) Then
        Set ws = wb.Worksheets("Sales")
        MsgBox "Sales exists. Its tab name is " & ws.Name
    Else
        MsgBox "The Sales worksheet was not found."
    End If
End Sub

Save a copy of the workbook before running a macro, especially before adapting the example to edit or create sheets. VBA changes cannot be undone with Ctrl+Z (or Command+Z on a Mac); closing without saving may be the only way to discard unintended changes.

What the check is doing

A workbook’s Worksheets collection contains its worksheet tabs. The expression wb.Worksheets(sheetName) asks that collection for a worksheet whose tab name matches the supplied text. When the name is found, Set assigns the object to ws. When it is absent, VBA raises a lookup error, so the function uses On Error Resume Next for that one assignment and then immediately restores normal error handling with On Error GoTo 0.

The final test, Not ws Is Nothing, checks whether the object reference was successfully assigned. A worksheet can exist even if it is hidden or very hidden; visibility controls whether a user can see its tab, not whether it belongs to the Worksheets collection. This is an existence check, not a check that the sheet is visible, usable for a particular task, or contains expected data.

Because On Error Resume Next can hide other errors if it is left active, keep its scope narrow. Avoid wrapping a long section of macro code in it. If a workbook reference is invalid or a later operation fails, you usually want VBA to report that problem rather than silently continue.

Choose the right workbook reference

The workbook argument determines where VBA looks. Two references are common, but they are not interchangeable:

• ThisWorkbook means the workbook that contains the running VBA project. It is usually the right choice for a macro stored in the same workbook as the worksheet it manages.
• ActiveWorkbook means the workbook currently active in Excel. Use it only when the macro is intentionally meant to act on whichever workbook the user has selected.

This distinction matters when code is stored in PERSONAL.XLSB, an add-in, or a separate controller workbook. In those cases, ThisWorkbook refers to that storage file, not necessarily the workbook the user wants to inspect. Pass ActiveWorkbook when that behavior is intended, or pass a workbook variable already set to a specific file. If no workbook is specified, results can depend on what is active at the moment the code runs.

For example, set Set wb = ActiveWorkbook before calling the function if the active file is the intended target. A more controlled macro may open or receive a particular workbook and retain that object in a variable. Whichever option you choose, keep the same wb reference for both the check and the later worksheet operation.

Alternative: loop through the worksheets

A loop avoids relying on an expected lookup error. It can be easier to understand when a macro already iterates through every worksheet, though for a one-off name check it involves more code:

Public Function WorksheetExistsByLoop(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
    Dim ws As Worksheet

    ' Reject an unusable workbook reference or an empty name.
    If wb Is Nothing Then Exit Function
    If Len(sheetName) = 0 Then Exit Function

    ' Compare each tab name without distinguishing letter case.
    For Each ws In wb.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            WorksheetExistsByLoop = True
            Exit Function
        End If
    Next ws
End Function

For normal workbooks, either method is suitable. The collection lookup is compact and familiar; the loop makes the comparison rule visible and can be adapted to inspect multiple properties, such as each sheet’s visibility. The loop uses vbTextCompare, which performs a text comparison without distinguishing letter case. Excel worksheet names are not distinguished by case, so this is generally the expected behavior for a name check.

Do not use the loop merely to make a case-sensitive sheet-name test unless that is a deliberate separate requirement. Excel does not allow two worksheet tabs whose names differ only by letter case. Also note that comparing a sheet name to a value with extra spaces will not automatically trim those spaces. Decide whether spaces are meaningful in your input rather than silently changing the requested name.

Check first, then create a missing worksheet

A common follow-up is to create a worksheet only when it is absent. Check first to prevent a duplicate-name error, then add and name the sheet. The example below adds the new worksheet to the workbook passed to the function:

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

    ' Work only in the workbook that contains this macro.
    Set wb = ThisWorkbook

    If Not WorksheetExists("Summary", wb) Then
        Set ws = wb.Worksheets.Add
        ws.Name = "Summary"
    End If
End Sub

If Summary already exists, the code leaves it untouched. If it does not, the new worksheet is added and renamed. A name that is invalid under Excel’s worksheet naming rules, or a workbook state that prevents adding or renaming sheets, can still cause an error; an existence check does not guarantee that every later operation will succeed. For example, workbook structure protection can restrict adding, deleting, moving, or renaming sheets.

Common mistakes and edge cases

• Checking the wrong collection: Worksheets contains worksheet tabs, whereas Sheets includes both worksheets and chart sheets. If the question is specifically whether a worksheet exists, Worksheets is the appropriate collection. A chart sheet with the requested name does not make WorksheetExists return True.
• Assuming hidden means absent: Hidden and very hidden worksheets remain in Worksheets, so the function returns True for them. Check the worksheet’s Visible property separately if visibility matters.
• Using an unqualified lookup: Worksheets("Sales") depends on the active workbook. wb.Worksheets("Sales") makes the target explicit and is safer in multi-workbook code.
• Ignoring errors too broadly: On Error Resume Next should surround only the lookup. Restore normal handling immediately, as in the function, so unrelated faults are not concealed.
• Expecting an empty or misspelled name to be found: A blank string returns False in the supplied function. A typo, extra space, or wrong workbook also produces False; confirm the exact tab name and target workbook before treating that result as proof that a sheet should be created.

Sheet names are limited by Excel’s naming rules, so a name passed to a creation routine may be rejected even if the existence check correctly says it is absent. Keep checking and creation as separate decisions: the first answers whether a sheet is present, while the second must still obey workbook protection and naming requirements.

Frequently Asked Questions

Does WorksheetExists detect hidden worksheets?
Yes. Hidden and very hidden worksheets are still members of the workbook’s Worksheets collection. The function checks whether the tab exists, not whether it is visible.

Is ThisWorkbook the same as ActiveWorkbook?
No. ThisWorkbook is the file containing the VBA code; ActiveWorkbook is the file currently active in Excel. Choose the reference that identifies the intended target, particularly when using an add-in or PERSONAL.XLSB.

Can I use this to check whether a chart sheet exists?
Not with a function that searches Worksheets. Chart sheets belong to the broader Sheets collection. Use a separate check against wb.Sheets if chart sheets should count, and account for the fact that the result could be a chart sheet rather than a Worksheet.

Does the function care whether the name uses uppercase or lowercase?
Excel worksheet names are not distinguished by letter case, and collection lookup follows that behavior. The loop version explicitly uses vbTextCompare for a case-insensitive text comparison.

Can I check and add the worksheet in one macro?
Yes. Call WorksheetExists with the target name and workbook, then use Worksheets.Add and assign the name only when the function returns False. The creation can still fail if the workbook is protected or the proposed name is invalid.

Why does my check return False even though I can see the tab?
The macro may be checking a different workbook, or the supplied text may not match the tab name, including spaces. Confirm the wb reference and the exact worksheet tab text, then use that same workbook variable for the check and subsequent actions.

Related

• Microsoft Excel Scatter Plot: How to Create and Read One
• Microsoft Excel Power Query: What It Does and How to Use It
• Microsoft Excel Print Area: Set, Change, and Clear

More in VBA & macros

• VBA MsgBox: Create an OK or Decline Box?Yes. In VBA, use MsgBox with the vbOKCancel button style to show OK and Cancel, or vbYesNo to…
• Excel VBA: Find a String Within a StringUse VBA’s InStr function to find one string inside another: it returns the character position of the first…
• Excel VBA: Find the Last Cell with Data in a ColumnIn Excel VBA, use Cells(Rows.Count, columnNumber).End(xlUp).Row to find the last nonblank cell’s row in a column. Check for…
• VBA AutoFilter: Filter Text with Like PatternsTo filter rows in Excel VBA for text that is like a pattern, use the AutoFilter method with…
• VBA Copy and Paste Values Across All Excel Tabs SafelyYes. In Excel for Microsoft 365 on Windows, a VBA macro can take the selected range on the…
• Excel VBA: Copy and Paste Range ValuesTo copy a range’s results without its formulas or formatting, assign the source range’s Value property to a…

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

Spotted an error in this guide? Let us know.