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

 

[shg]

1. Are you using the exact code posted by Joe, or did you change it in any way?

2. Is the workbook protected?

3. Are any worksheets protected?

I assume you know that the index order of sheets is the tab order seen by the user; if they move sheets around, the last N may not be the last N sheets added.

 

[Mr. Wizard]

I think I have figured out some of the issues that are killing the process of this.

I have been creating worksheets by copying data from a range of cells into another worksheet and pasted special as values which generates a chart. I copied this worksheet compete with embedded charts to another worksheet and renamed it programatically.

Excel gets confused as to the sheets actual identification.

When I reworked the macro to start at Sheet(1) to delete, it deleted one of the sheets that I generated in this fashion, which in the listing in VBA shows as sheet(5). Excel was apparently very confused, because the next one deleted was Sheet(2). But this time it actually deleted Sheet(2).

It is just a guess, but if I deleted Sheet(1), and then skipped to the top number and worked backwards, I think it would clear all of the sheets I wanted gone. Kind of a kludge though.

I will test this more and let you know.

The take-away from this is that it makes a big difference how you create worksheets when it comes to Excel identifying them correctly for programaticly deleting them.

 

[Mr. Wizard]

I used his code as listed in my original spreadsheet, and it errored out. I created a new spreadsheet and then copied the contents of my original spreadsheet into the new tabs.

I have been creating copies of existing worksheet tabs and adding them at the front (left) of the workbook.

Once I created the new workbook, the tabs then started deleting, but not in the order expected. It took the far left tab, then jumped to the second tab I created in the new workbook which was 2nd from the right for the user.

I have a chart with very complex formatting, and I was copying the entire tab that contained the data to create the chart and the chart itself. This way the chart source references did not need to be changed, and the charts always maintain their formatting.

I was expecting to delete the tabs numerically in the order they appear in the VBA window where they are listed as Sheet(1)Input Sheet(2)Chart etc....

Still the copied tabs produced some unpredictable results.

 

[shg]

The index order of sheets is the tab order seen by the user. The order of appearance in the Project Explorer window is alphabetical by CodeName. They may be completely different.

I've never seen Excel get confused about either.

 

[Joe4]

The index order of sheets is the tab order seen by the user.

This is an important point that shg makes. So my code will keep the first five sheets, as seen on the Excel workbook (not in VBA).

I tried inserting sheets all over the place (at the beginning, in the middle, and at the end), and it consistently left the 5 left-most sheets as seen by the user in th workbook, and deletes all the other ones (with no errors). That is what I thought you wanted to do.

Are you saying that you are deleting sheets all over the place, but instead of keeping the five left-most, you want to keep the 5 original, regardless of where they currently reside in your workbook?

 

[Joe4]

I have been creating copies of existing worksheet tabs and adding them at the front (left) of the workbook.

I just noticed this line here. If I understand it correctly, it sounds like maybe you want to delete from the left, so you are always leaving the right-most 5 sheets. Is that right? If so, this slight code modification should do that.

Sub MyDeleteSheets2()

'   Deletes all sheets except the last 5
 
    Dim myNumSheets As Integer
    Dim i As Integer
    
'   Count all sheets in workbook
    myNumSheets = Sheets.Count
    
'   Delete all sheets except the right most 5
    If myNumSheets > 5 Then
        For i = (myNumSheets - 5) To 1 Step -1
            Application.DisplayAlerts = False
            Sheets(i).Delete
            Application.DisplayAlerts = True
        Next i
    End If
    
End Sub

 

[Mr. Wizard]

YOU ASKED:

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

Yes, all of the sheets that I keep are always named the same.

THIS IS IT.....!!!! This solution should work no matter what name or number the worksheets had.

I would suppose that I could test the name of each worksheet against conditions with an OR statement, and if it is FALSE, delete it.

I think I could figure out the code on this one now. Once I test it out, I will post it.

 

[Joe4]

What about the last question I asked?

I just noticed this line here. If I understand it correctly, it sounds like maybe you want to delete from the left, so you are always leaving the right-most 5 sheets. Is that right? If so, this slight code modification should do that.

If that is true, that modified code I posted should do what you need (without having to check the name of each sheet).

 

[Mr. Wizard]

I was not interested in just the right hand 5 tabs. I was interested in keeping the core of the workbook that I needed to generate the charts. There are 3 tabs, one of them very hidden.

The following code finally worked:

<p class="MsoNormal">Code:</p>
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%" style="width: 100.0%">
<tr>
<td style="border: 1.0pt inset; padding: 4.5pt; background: #E6E6E6">
<p class="MsoNormal">Sub MyDeleteSheets()</p>
<p class="MsoNormal">'   Deletes all sheets that are not a core part of
the template<br>
    Dim myNumSheets As Integer<br>
    myNumSheets = Sheets.Count<br>
'   Delete all sheets if not titled correctly titled as permanent sheets</p>
<p class="MsoNormal">        For i = myNumSheets To 1 Step -1</p>
<p class="MsoNormal">            If Not ((Sheets(i).Name = "Input Data")
Or (Sheets(i).Name = "Impact Chart") Or (Sheets(i).Name = "Milestone
Data")) Then<br>
            Application.DisplayAlerts = False<br>
            Sheets(i).Delete<br>
             Application.DisplayAlerts = True</p>
<p class="MsoNormal">            Else<br>
'               Do nothing if the sheet title is one to save<br>
            End If<br>
        Next i<br>
End Sub</td>
</tr>
</table>
<p class="MsoNormal"> </p>

</body>

</html>