check if worksheet exists

[Guest]

I have a very simple subroutine that deletes certain worksheets.
Unfortunately, the macro crashes if the worksheet does not exist (not the
usual case, but does happen).

Sheets("Summary").Select
ActiveWindow.SelectedSheets.Delete
....and so on, with other sheet names (but not all the sheet names)...

I tried:
If Sheets("Summary") Is Nothing Then
....
End If
That gives me a "Run-time error '9': Subscript out of range" error.

How can I check to see if this sheet exists before trying to delete it?

Thank you,
Judy

 

[Ron de Bruin]

Hi Judy

You can use a function like this in a normal module

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function


and use
If SheetExists(yourstring) = False Then.....


Or use a on error

Sub test()
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Sheet2").Delete
Application.DisplayAlerts = True
On Error GoTo 0
End Sub

 

[Guest]

I really appreciate your quick response. You solved my problem.

Thank you,
Judy