How do you check if a sheet exists in Excel VBA?

VBA Code to Check if Sheet with Name exists?

This Excel vba code will check if sheet with a specific name exists in the workbook.

As per Microsoft specifications, You can add as many number of sheets depending on the limits of Your computer’s memory.

So, this loop will check if any of those sheets has the name passed as parameter to this function.

• There are 2 methods presented in this page.
• Use option 1 first & Test the performance.
• If the loop is taking more time, then use 2nd option.

VBA function to Check if Sheet Exists

Here is the vba code the loop thru all the sheets & verifies the name passed as parameter.

Public Function SheetExists(SheetName As String) As Boolean
    'Declare variables - Officetricks.com
    Dim wSh As Worksheet
    Dim bReturnValue As Boolean
    
    'Loop Thru Each Sheet in workbook
    bReturnValue = False
    For Each wSh In ThisWorkbook.Sheets
    
        'Check whether there is a name match
        If VBA.UCase(wSh.Name) = VBA.UCase(SheetName) Then
            bReturnValue = True
            Exit For
        End If
    Next wSh
    
    'Return Match Result
    SheetExists = bReturnValue
End Function

The above function will return ‘True’ if the sheet with exact name exists in the workbook. Otherwise it will return false.

Lets see another version that is more faster than the above one.

VBA Worksheet Exists with specific Name in Workbook

This function does not loop thru all existing sheet. It directly checks for the Sheet name.

If you face any issue in using this function, then use the first one. Option 1 gives good results consistently.

Public Function fSheetExists(SheetName As String) As Boolean
    'Declare variables - Officetricks.com
    Dim wSh As Worksheet
    Dim bReturnValue As Boolean
    Set wSh = Nothing
    
    'Assign Sheet to Object
    On Error Resume Next
    Set wSh = ThisWorkbook.Sheets(SheetName)
    On Error GoTo 0
    
    'Check if Sheet with name exists
    If wSh Is Nothing Then
        bReturnValue = False
    Else
        bReturnValue = True
    End If
    
    'Return Match Result
    fSheetExists = bReturnValue
End Function

Using this function You can confirm is a sheet exists of not.

Then by using the function in here, you can create or add new worksheet to workbook & rename it. Click here to get the code to add new sheet & rename it.

Public Function SheetExists(strSheetName As String, Optional wbWorkbook As Workbook) As Boolean
    If wbWorkbook Is Nothing Then Set wbWorkbook = ActiveWorkbook 'or ThisWorkbook - whichever appropriate
    Dim obj As Object
    On Error GoTo HandleError
    Set obj = wbWorkbook.Sheets(strSheetName)
    SheetExists = True
    Exit Function
HandleError:
    SheetExists = False
End Function

Fluff

MrExcel MVP, Moderator

• #3

Maybe something like

VBA Code:

Dim ShtName As String
ShtName = "Sheet 1"
If Evaluate("isref('" & ShtName & "'!A1)") Then
   'sheet exists do something
Else
   'sheet doesn't exist do something else
End If

Fluff

MrExcel MVP, Moderator

• #5

Glad to help & thanks for the feedback.

Fluff

MrExcel MVP, Moderator

• #7

How about

VBA Code:

Dim ShtName1 As String, ShtName2 As String
ShtName = "Sheet 1"
ShtName = "Sheet 2"
If Evaluate("isref('" & ShtName1 & "'!A1)") Then
   'sheet exists do something
ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
   'sheet doesn't exist do something else
Else
   'whatever
End If

Fluff

MrExcel MVP, Moderator

How do you check if a sheet exists in VBA Excel?

Is exist in VBA?

How do I find a specific sheet name in VBA?

How can I tell if a sheet is active in Excel?