Here's the code to loop through the sheets to check whether the name
exists or not.
Sub CheckIfSheetNameExists(ByVal strCrit As String)
'
Dim CurSheet As Worksheet
Dim NameExists As Boolean

NameExists = False
For Each CurSheet In Sheets
If InStr(1, CurSheet.Name, strCrit) Then
NameExists = True
End If
Next CurSheet
If Not NameExists Then
MsgBox "Name does not exist. please fix"
Exit Sub
End If
End Sub

here's the code to test the procedure :
Sub test()
CheckIfSheetNameExists ("ExcelPragma.com")
End Sub

HTH

Fadi
www.chalouhis.com/XLBLOG