grouping sheets

[Hans]

Im writing a macro that groups a number of sheets in an
existing workbook and performs some action on them.

I want to add a macro that deletes all the sheets that do
not belong to this group. What is the best way to do this?
The number of sheets that do not belong to the group is
not always the same so I cannot select them on sheet name
or number.

Thanks for your help.

regards
Hans

 

[Bob Phillips]

Hi Hans,

Here is some code

Sub Grouped()
Dim sh As Worksheet
Dim sh2 As Worksheet
Dim fFound As Boolean

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
fFound = False
For Each sh2 In ActiveWindow.SelectedSheets
If sh.Name = sh2.Name Then
fFound = True
Exit For
End If
Next sh2
If Not fFound Then sh.Delete
Next
Application.DisplayAlerts = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Hans]

Hello Bob

Thanks. I will try it out.

regards,
Hans

 

[Norman Jones]

Hi Bob,

Except in special cases, I believe that this will fail.It fails, for example
if a multiple or non-contiguous selection does not include the lowest index
sheet..
The code will fail whenever a sheet is deleted which has with a lower index
than that of a grouped sheet. This is because the deletion removes the
worksheet grouping.
One solution to this might be to put the grouped sheets into an array and
use the array for comparison purposes.

 

[Norman Jones]

Hans,

Try:

Sub Grouped2()
Dim sh As Worksheet
Dim fFound As Boolean
Dim groupedArr() As Variant
Dim i As Long

ReDim groupedArr(1 To ActiveWindow.SelectedSheets.Count)
For i = LBound(groupedArr) To UBound(groupedArr)
groupedArr(i) = ActiveWindow.SelectedSheets(i).Name
Next

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
fFound = False
For i = LBound(groupedArr) To UBound(groupedArr)
If sh.Name = groupedArr(i) Then
fFound = True
Exit For
End If
Next i
If Not fFound Then sh.Delete
Next
Application.DisplayAlerts = True

End Sub

 

[Bob Phillips]

Hi Norman,

Yes, that makes sense. As you say, the way to circumvent this would be to
build an array and check against that.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)