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

I want to write a macro that selects all the sheets in a workboo
EXCEPT the active one (which can be ANY of the sheets) and delete
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 reache
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!

/

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

Try this

Sub test()
For Each sh In ThisWorkbook.Sheets
If sh.Name < ActiveSheet.Name Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"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/

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/

thanks a lot!! it worked prefectly :

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

First of all you need to do it backwards, you also need
to save the active sheets name/index, you don't want to
delete that.Try this

Sub DeleteSheets()
Dim a, i, NrWs As Integer
'Save the activesheets name
AWs = ActiveSheet.Name
Debug.Print AWs
a = Workbooks.Count
NrWs = Worksheets.Count
For i = NrWs To 1 Step -1
If Worksheets(i).Name < AWs Then
Debug.Print NrWs, i
Workbooks(a).Worksheets(i).Select
ActiveWindow.SelectedSheets.Delete
End If
Next i
End Sub

Regards
Lars Kofod
-----Original 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/

.

Thanks for that. You answered my question "How do I get VB to delete an
Excel worksheet without asking first".

Simon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!