Re: 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
.



Relevant Pages

  • Re: lookup for bright solution...
    ... Dim wCtr As Long ... Dim ArrNames() As String ... Dim iCtr As Long ... Dim mySelectedSheets As Sheets ...
    (microsoft.public.excel.programming)
  • Re: Help with Worksheet Merging Macro
    ... If Not MWS Is AWS Then ... Ray517 in the array of sheets to be combined, ... Dim mySheetNames As Variant ... Dim MWS As Worksheet 'Worksheet to be merged ...
    (microsoft.public.excel.newusers)
  • Re: Help with Worksheet Merging Macro
    ... If Not MWS Is AWS Then ... Ray517 in the array of sheets to be combined, ... Dim mySheetNames As Variant ... Dim MWS As Worksheet 'Worksheet to be merged ...
    (microsoft.public.excel.newusers)
  • Re: Help with Worksheet Merging Macro
    ... How do I insert the Option Explicit into the marco? ... If I run a macron on the workbook which has up to 30 sheets I don't know if the sheet exists until an error is generated, so I wanted to prevent that from happening. ... Dim mySheetNames As Variant ... Dim MWS As Worksheet 'Worksheet to be merged ...
    (microsoft.public.excel.newusers)
  • Re: protect all sheets macro crashes when sheet is hidden
    ... Sub FillUpCellsTest() ... Dim LoopCounter as Integer ... you didn't even work with sheets unhidden. ...
    (microsoft.public.excel.programming)