Often when you are working with Excel VBA you need to confirm if a sheet exists based on the tab name. This Function can be used to do that. It works with the active file (workbook).
The function below is meant to be used within another macro, an example of that follows.
Private Function SheetExists(sName As String) As Boolean
'returns TRUE if the sheet exists
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sName)
SheetExists = (Err.Number = 0)
Set x = Nothing
End Function
To use this function in a macro, the code would look something like. This is looking for a sheet tab named test.
Sub TestSheet()
If SheetExists("test") Then
MsgBox "sheet exists"
'do stuff
Else
MsgBox "sheet doesn't exist"
'tell user to fix something
End If
End Sub