If Sheet Exists Error

[ptmuldoon]

I'm trying to test if a Sheet Exists, and if it does exits to do some additional code/work on it. Any reason why the below fails with a 'Subscript out of Range' Error when the sheet in question is missing?

If I change the Sheet name to something that does exists, the code and msgbox returns correctly. But when the sheet being looked for is actually missing it gives the error?

Sub Test()
    If Not Sheets("Evaluation") Is Nothing Then
        MsgBox "Sheet Exists"
    Else
        MsgBox "Missing"
    End If
End Sub

Edit. I think I understand now. This is because it is looking at the ActiveSheet only? And you need to actually loop through the workbook to check if the worksheet exists?

[Faridwahidi]

Hi ptmuldoon,

you may try this,

Sub checkSheet()
    If Not SheetExists("Evaluation") Then
        MsgBox "Missing", vbInformation, "Sheet Not Found"
    End If
End Sub

Function SheetExists(WorkSheetName) As Boolean
    SheetExists = False
    For Each ws In Sheets
        If UCase(ws.Name) = UCase(WorkSheetName) Then SheetExists = True
    Next ws
End Function

If this helped you, please add reputation to me

[Ron Coderre]

I use this:

Function SheetNameExists(strShtName As String, Optional strWkbkName) As Boolean
If IsMissing(strWkbkName) Then
   strWkbkName = ThisWorkbook.Name
End If
    
On Error Resume Next
SheetNameExists = Workbooks(strWkbkName).Sheets(strShtName).Parent.Name <> ""
        
End Function

Is that something you can work with?

[LJMetzger]

Hi,

The subscript Out of Range runtime error is generated when the Sheet does not exist.

I use the following function:

Public Function LjmSheetExists(SheetName As String) As Boolean
'Return value TRUE if sheet exists

  On Error Resume Next

  If Sheets(SheetName) Is Nothing Then
    LjmSheetExists = False
  Else
    LjmSheetExists = True
  End If
  On Error GoTo 0
  
End Function

Lewis

[ptmuldoon]

Both are good solutions. Code is running now through about 100 files to see if the sheets exists and pull out some data.

[Faridwahidi]

Hi pthmuldoon,

Thanks for rep'