Check if sheet exists problem

If a sheet exists I want to delete it. Why doesn't this work, and is there a better way to do it?

I mean for now I can work around it with error trapping, but in my experience error trapping is a bad way of coding.

If Not Sheets("Master Equipment List") Is Nothing Then
Application.DisplayAlerts = False
Sheets("Master Equipment List").Delete
Application.DisplayAlerts = True
Else
End If

Oh if it helps, I get a "subscript out of range" error on the "If Not...Then" line.

Hi there,

Looking at your code, two possibilities suggest themselves:

1. Are you absolutely sure that the name on the worksheet tab corresponds to the string value that you've entered in your code? To avoid the possibility of spelling and/or case differences run the following code:

Please Login or Register  to view this content.

If you don't get a "Found" message then check the name on the tab.


2. Are you sure that the workbook from which you want to delete the worksheet is the active workbook when the code is executed?

If any other workbook might be active, then use "Thisworkbook.Sheets("Master Equipment List")" to fully specify the required worksheet.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Naw, the name is correct because when the sheet is present it works fine. So far I've done a work around with this....

on error goto nosheet
If Not Sheets("Master Equipment List") Is Nothing Then
Application.DisplayAlerts = False
Sheets("Master Equipment List").Delete
Application.DisplayAlerts = True
Else
End If
nosheet:

But it seems to defeat the point of having an if/then statement.

Hello Wilro85,

Sometimes you have to trap an error in the code. In this case, the error occurs because the Sheets collection object raises the error internally whenever an index or key value isn't found. Here is another workaround that avoids the error.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hi,
you can simply try to delete this sheet with On Error Resume Next - if the sheet not exists, the rest of code continues.
Another issue - the workbook has to be NOT PROTECTED.

Jarek

Please Login or Register  to view this content.

Oops - sorry! I hadn't realised that the code worked when the worksheet existed.

Greg M

Ok, this is just a bit of a noob followup question, but when you say...

"on error goto 0"

where does the "0" tell it to go?

Hello Wilro85,

The statement "On Error GoTo 0" returns control of the error handling back to the system. The statement "On Error Resume Next" allows you trap and examine an error you might expect to encounter when accessing an object, or might be raised from user actions. Wasn't the point of your post to avoid error handling???

Sincerely,
Leith Ross