Deleting all added worksheets

[Mr. Wizard]

Before using a macro to create more worksheets that contain charts and data, I would like to clear out any existing worksheets.

I would like to use VBA to delete all worksheets in a range with a sheet index number that is higher than a specified number.

Sheets(1) through Sheets(5) are sheets to preserve because they contain data.

Sheet(6) is the lowest possible sheet index number, but may not exist if the user previously deleted it.

I do not know what the highest sheet number will be, since the user can add or remove worksheets that I need to delete.

'Delete existing charts
Application.DisplayAlerts = False
Countsheets = Application.Sheets.Count
If Countsheets > 5 Then
For Sheetkill = 6 To Countsheets
On Error GoTo ErrHandler
Sheets(Sheetkill).Activate
ActiveWindow.SelectedSheets.Delete
ErrHandler:
Next Sheetkill
End If

 

[Joe4]

I think this should work.

Sub MyDeleteSheets()
'   Deletes all sheets after 5th sheet
 
    Dim myNumSheets As Integer
    Dim i As Integer
    
'   Count all sheets in workbook
    myNumSheets = Sheets.Count
    
'   Delete all sheets after sheet 5
    If myNumSheets > 5 Then
        For i = myNumSheets To 6 Step -1
            Application.DisplayAlerts = False
            Sheets(i).Delete
            Application.DisplayAlerts = True
        Next i
    End If
    
End Sub

 

[Mr. Wizard]

Thanks! The code looks similar to another approach I took. I like this solution better though. I will give it a try.

 

[Mr. Wizard]

I tried the method you suggested.

When I ran the code, it got error 1004 Delete method of Worksheet class failed.

 

[Joe4]

That's odd, it works for me. Which version of Excel are you using?

Maybe "chart sheets" are handled a little differently. Maybe try incorporating the method you usedm where you activate the sheet first to see if it makes any difference.

Sub MyDeleteSheets()
'   Deletes all sheets after 5th sheet

    Dim myNumSheets As Integer
    Dim i As Integer
    
'   Count all sheets in workbook
    myNumSheets = Sheets.Count
    
'   Delete all sheets after sheet 5
    If myNumSheets > 5 Then
        For i = myNumSheets To 6 Step -1
            Application.DisplayAlerts = False
            Sheets(i).Activate
            ActiveWindow.SelectedSheets.Delete
            Application.DisplayAlerts = True
        Next i
    End If
    
End Sub

 

[Mr. Wizard]

I am using Excel 2007. I looked at the code and it should work from everything I've seen.

I am not trying to delete chart tabs, although the method would be quite similar.

 

[Joe4]

I am using Excel 2007 also. Are any of the sheets protected, or are the moved around or added in a strange order?

 

[Mr. Wizard]

Thanks for the effort, but it still bombed out on me. I also tried to select the sheet as well as activate it.

 

[Joe4]

See the questions I asked in my last post.

Will all 5 sheets you are keeping always have the same name? If so, we cn loop throught all sheets and delete the ones that do not have those names.

 

[Mr. Wizard]

The worksheets are named dynamically by my program, and I have a list of the data used to generate the names, so that I can cycle through them. However if a user deletes some of the data, that would not work.