select and delete sheets

  • Thread starter Thread starter Locachica
  • Start date Start date
L

Locachica

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!

/
 
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
 
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
 
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
 
Thanks for that. You answered my question "How do I get VB to delete an
Excel worksheet without asking first".

Simon
 
Back
Top