Delete Sheets
 

I have this vba code that allows me to delete all the sheets but the one is
active.

Is there a way to delete all sheets but the ones "selected"??

workbook has 4 sheets: 1,2,3,4
If I select 2 and 3, delete 1 and 4

----------------------------------------
'Delete all but active sheet
For Each sheete In Sheets
If ActiveSheet.Index < sheete.Index Then
Application.DisplayAlerts = False
sheete.Delete
Application.DisplayAlerts = True
End If
Next sheete

Delete Sheets
 

One way that loops through the selected sheets in the activewindow:

Option Explicit
Sub testme01()
Dim sh As Object
Dim SelSheet As Object
Dim SelSheets As Sheets
Dim IsSelected As Boolean

Set SelSheets = ActiveWindow.SelectedSheets
For Each sh In ActiveWorkbook.Sheets
IsSelected = False
For Each SelSheet In SelSheets
If sh.Name = SelSheet.Name Then
IsSelected = True
Exit For
End If
Next SelSheet
If IsSelected Then
'skip it
Else
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End Sub

Another way is to build an array of those sheetnames and just compare each sheet
name against that list of names:

Option Explicit
Sub testme02()
Dim sh As Object
Dim res As Variant
Dim iCtr As Long
Dim SelSheetNames() As String
Dim SelSheets As Sheets

Set SelSheets = ActiveWindow.SelectedSheets
ReDim SelSheetNames(1 To SelSheets.Count)
For iCtr = 1 To SelSheets.Count
SelSheetNames(iCtr) = SelSheets(iCtr).Name
Next iCtr

For Each sh In ActiveWorkbook.Sheets
res = Application.Match(sh.Name, SelSheetNames, 0)
If IsNumeric(res) Then
'not in the selected sheets
Else
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End Sub

Bre-x wrote:

I have this vba code that allows me to delete all the sheets but the one is
active.

Is there a way to delete all sheets but the ones "selected"??

workbook has 4 sheets: 1,2,3,4
If I select 2 and 3, delete 1 and 4

----------------------------------------
'Delete all but active sheet
For Each sheete In Sheets
If ActiveSheet.Index < sheete.Index Then
Application.DisplayAlerts = False
sheete.Delete
Application.DisplayAlerts = True
End If
Next sheete

--

Dave Peterson

Delete Sheets
 

Why not select the ones TO delete.

Sub deleteSELECTEDshts()
Application.DisplayAlerts = False
For Each WS In ActiveWindow.SelectedSheets
WS.Delete
Next
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bre-x" wrote in message
...
I have this vba code that allows me to delete all the sheets but the one is
active.

Is there a way to delete all sheets but the ones "selected"??

workbook has 4 sheets: 1,2,3,4
If I select 2 and 3, delete 1 and 4

----------------------------------------
'Delete all but active sheet
For Each sheete In Sheets
If ActiveSheet.Index < sheete.Index Then
Application.DisplayAlerts = False
sheete.Delete
Application.DisplayAlerts = True
End If
Next sheete

Delete Sheets
 

Sticking with the Selected Sheets idea of the other two posters, you can do
it with this simpler macro...

Sub DeleteSelectedSheets()
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub

Be careful with it, though... it will delete the active sheet if you run it
by mistake and having the DisplayAlerts set to False means it will not warn
you first.

--
Rick (MVP - Excel)


"Bre-x" wrote in message
...
I have this vba code that allows me to delete all the sheets but the one is
active.

Is there a way to delete all sheets but the ones "selected"??

workbook has 4 sheets: 1,2,3,4
If I select 2 and 3, delete 1 and 4

----------------------------------------
'Delete all but active sheet
For Each sheete In Sheets
If ActiveSheet.Index < sheete.Index Then
Application.DisplayAlerts = False
sheete.Delete
Application.DisplayAlerts = True
End If
Next sheete

Delete Sheets
 

Thank you all.



"Bre-x" wrote in message
...
I have this vba code that allows me to delete all the sheets but the one is
active.

Is there a way to delete all sheets but the ones "selected"??

workbook has 4 sheets: 1,2,3,4
If I select 2 and 3, delete 1 and 4

----------------------------------------
'Delete all but active sheet
For Each sheete In Sheets
If ActiveSheet.Index < sheete.Index Then
Application.DisplayAlerts = False
sheete.Delete
Application.DisplayAlerts = True
End If
Next sheete