SOLVED deleting certain sheets

[LodBot]

I need to erase any sheet that starts with "Report" but isn't "Report - Total", so I tried this code:

For a = 1 To ActiveWorkbook.Sheets.count
    If Left(ActiveWorkbook.Sheets(a).Name, 6) = "Report" And ActiveWorkbook.Sheets(a).Name <> "Report - Total" Then
        ActiveWorkbook.Sheets(a).Select
        ActiveWindow.SelectedSheets.Delete
    End If
Next

When the code runs, it erases 2 of the correct sheets, but I get an error once it reaches the 3rd sheet. The error is "1 Subscript out of range" and occurs on the second line. Got any ideas?

Thanks,
Alex

[DrTechnology]

Hello Alex

It is my pleasure to answer your question, and my answer may sound odd at first but trust me, its the right one...

You said that you were getting an error "Run-Time Error 9, Subscript out of range", well no wonder here is why:

You are using a For-Next Loop which increments 1 to the counter every time you pass through it.

For a = 1 to ... each time you loop, a becomes a+1...

BUT

Your target for a is ActiveWorkbook.Sheets.count, which decreases by one each time you do
ActiveWindow.SelectedSheets.Delete

thus a will become 6 at the 5th worksheet being deleted, and since you are using the index of the worksheet, you fail, because
ActiveWorkbook.Sheets.count evaluates to 5.

There is no 6th worksheet when you only have 5 worksheets in a workbook.

OK your solution is to use the For-Next Backwards...

For a = ActiveWorkbook.Sheets.Count To 1 Step -1

so the number of worksheets is evaluated, first and then the counter is incremented back by 1 with the Step clause.

Hope this makes sense to you.

Now in addition to my reply, I have given you a better code here. I say better based on experience and nothing more. I am sure someone can come up with even better code than mine.

Option Explicit

Sub DeleteSheets()
Dim shtWorksheet As Worksheet '/Declare all your variables

'/Stop the Application from displaying any alerts.
Application.DisplayAlerts = False
For Each shtWorksheet In ActiveWorkbook.Sheets
If Left(shtWorksheet.Name, 6) = "Report" And _
shtWorksheet.Name <> "Report - Total" Then
shtWorksheet.Delete
End If
Next shtWorksheet

'/Restore the Application to displaying alerts.
Application.DisplayAlerts = True
End Sub

Hope this starts you with using Objects when dealing with Objects.

Dr. Technology

[LodBot]

works

Thanks for all the help! That was EXACTLY what I was looking for!

Alex