Check if sheet exists and delete it using IF ERROR function in VBA

**Jardim** · 09-25-2012, 09:51 AM

Hi Guys,

i need some help writing a rather simple code to check if a sheet exists and the delete it using the IF ERROR function (if its the easier way...).
I'm working on a workbook that is intended to be like a excel management report. It has several sheets with pivot tables filtering the database information in different ways. Because the workbook will be used by a large number of people, who often double-click on the pivots to see the details of some numbers in the report, I'd like to have amacro to save the file (in the same location) after modifications are done, but deleting the extra sheets created (Sheet 1, Sheet 2, etc...).

However, I don't know - and haven't found it in forums yet - the syntax for the IF ERROR function.
Is there anyway i can do that using this function?

What I thought of is something like this (i know this is not a proper code, it's just the main idea....)

for i = 1 to 10
if Sheets("Sheet"&i).Select Is error
then Next i
else Sheets("Sheet"&i).Select
ActiveWindow.SelectedSheets.Delete
end if
Next i
ActiveWorkbook.Save

Can this be done? Or is the function only used to check cell values?

Thanks!

**patel45** · 09-25-2012, 10:12 AM

Sub deletesheets()
For j = 1 To Sheets.Count
  If Sheets(j).Name <> "Master" Then Sheets(j).Delete
Next
End Sub

**Jardim** · 09-25-2012, 10:31 AM

Hi Patel,

thanks for the quick response. The code works, but it deletes more sheets that i was supposed to. It should only delete the new sheets that Excel automatically labels "Sheet1", "Sheet2", an so on, but is also deletes the ones called "Revenue".

Then, after deleting a couple of them, it shows a "Run-time error '9': Subcript out of range" error message and highlights the text "If Sheets(i).Name <> "Master" Then" in yellow

Any idea what's happening?

Tks,.

**patel45** · 09-25-2012, 10:41 AM

Sub deletesheets()
For j = Sheets.Count to 1 step -1
  If Sheets(j).Name <> "Master" or Sheets(j).Name <> "Revenue" Then Sheets(j).Delete
Next
End Sub

Master and Revenue are the sheets you want preserve

**Jardim** · 09-25-2012, 11:19 AM

Still deletes all of them. Then it stops when there are no sheets left in the work book.
The code above seems right, but it's not keeping the "MAster" and "Revenue" sheets.

The way it is, isn't it going to error-stop when there are no more "Sheet+number" labeled sheets in the worbook?

**Andy Pope** · 09-25-2012, 11:51 AM

Sub deletesheets()
For j = Sheets.Count to 1 step -1
  If ucase(left(Sheets(j).Name,5)) = "SHEET" then Sheets(j).Delete
Next
End Sub

A better way would be for you to alter the code name of the sheets you want to retain. Perhaps add a prefix to those sheets.
The loop and check the codename rather than name before deleting.

You can change the code name of sheet object within the VBE.

**tigeravatar** · 09-25-2012, 11:52 AM

it should be an AND statement, not an OR statement.

**Jardim** · 09-25-2012, 12:06 PM

Cool, both suggestions work fine.
Changing OR for AND did it...

Using the prefix also worked...ìll be able to use it in another workbook i'm working on.

Tks a lot guys!