grouping sheets
 

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

grouping sheets
 

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" wrote in message
...
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

grouping sheets
 

Hello Bob

Thanks. I will try it out.

regards,
Hans


-----Original Message-----
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" wrote in
message
...
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


.

grouping sheets
 

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.

---
Regards,
Norman

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

grouping sheets
 

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

---
Regards,
Norman

"Hans" wrote in message
...
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

grouping sheets
 

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)

"Norman Jones" wrote in message
...
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.

---
Regards,
Norman

"Bob Phillips" wrote in message
...
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" wrote in message
...
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