delete all sheets but one

[akalei]

Hi, I'm trying to delete all the worksheets except for one called "macros" which contains all my macro buttons. I tried using this..

Sub delete_sheets()
bookname = ActiveWorkbook.Name

For i = 1 To ActiveWorkbook.Sheets.Count
Sheets(i).Select
If Sheets(i).Name <> "Macros" Then
ActiveSheet.Delete
End If
Next i

End Sub

I keep getting a subscript out of range error. Does anyone know what that means?

Thanks!

 

[zzjasonzz]

application.displayalerts = false
for each ws in thisworkbook.worksheets
if ws.name <> "Macros" then ws.delete
next ws
application.displayalerts = true

 

[Smitty]

Give this a shot instead:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> delete_sheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>                <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Macros" <SPAN style="color:#00007F">Then</SPAN> ws.Delete<br>            <SPAN style="color:#00007F">Next</SPAN> ws<br>        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I would imagine that the subscript out of range error has to do with the method you're trying to use in that you start with a fixed number of sheets, which you then delete, reducing the actual number of sheets in the process. So depending on what sheet you're on VBA is trying to delete a sheet that no longer exists and it bombs because it can't find it.

Hope that helps,

 

[akalei]

Thanks for the help!! It worked great and I understand what you're saying. I guess I was making my code more complicated than it needed to be.