Try something like the following

Dim WS As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each WS In Worksheets
If WS.Name < ActiveSheet.Name Then
WS.Delete
End If
Next WS
Application.DisplayAlerts = True
Application.ScreenUpdating = True



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Locachica" wrote in message
...

Hello!
I am new here.. have recently got into excel+vba. I have searched first
to see if I found an answer to my question.. but so far: nothing. So
sorry if I just didn't search enough and this question has appeared
before!

I want to write a macro that selects all the sheets in a workbook
EXCEPT the active one (which can be ANY of the sheets) and deletes
them, so I don't have to go manually and select them one by one..
sometimes the files have just 3 sheets, but others they have over 40..


This is what I wrote so far,

Sub DeleteSheets()
Dim a, i, NrWs as Integer
a = Workbooks.Count
NrWs = Worksheets.Count
For i = 1 To NrWs
Workbooks(a).Worksheets(i).Select
ActiveWindow.SelectedSheets.Delete
Next i
End Sub


When I run this macro, it deletes 3 of the sheets, but when it reaches
the 4th I get the following error:

Run-Time Error '9':
Subscript out of range

So I don't know what to do from here :(

Hope sb can help!!

Thanks a lot!

/J


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/