Grouping a range of sheets

[Stav19]

Hi All

What I'm trying to do is between a range of sheets ("Reports==>" and
"Pivots==>") ensure that the relevant columns and rows are grouped
together.

I've recorded the macro and that works fine, but I wanted to try and
do it using a macro that wasn't recorded...

Here's what I've got so far:

Sub ShtsGpd2()
'ShtsGpd2 Macro
'Slightly better method of grouping sheets together based on a range,
starting point etc.

Dim msheetsarray As Sheets
Dim sh As Worksheet

Set msheetsarray = Worksheets(Array("Reports==>", "Pivots==>"))

For Each sh In msheetsarray
With sh
.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
.Outline.ShowLevels RowLevels:=1
End With
Next

Sheets("Sheet1").Select
Range("B4").Select

End Sub

It loops through the sheets but doesn't actually do anything! When i
recorded the macro, there was an "activesheet." before the "outline"
above, but the vba commander wasn't liking that...

Any suggestions?

Cheers

 

[Dave Peterson]

I'm kind of confused at what you're doing...

If you manually grouped the sheets, you could use something like this to loop
through each of the sheets that are selected:

Dim sh As Object
For Each sh In ActiveWindow.SelectedSheets
MsgBox sh.Name
Next sh

If you want to just specify the first sheet of the group and the last sheet of
the group, you could use:

Dim FirstIndex As Long
Dim LastIndex As Long
Dim Temp As Long

Dim iCtr As Long

FirstIndex = Worksheets("Reports==>").Index
LastIndex = Worksheets("pivots==>").Index

If LastIndex < FirstIndex Then
Temp = FirstIndex
FirstIndex = LastIndex
LastIndex = Temp
End If

For iCtr = FirstIndex To LastIndex
MsgBox Worksheets(iCtr).Name
Next iCtr

 

[Stav19]

I'm kind of confused at what you're doing...

If you manually grouped the sheets, you could use something like this to loop
through each of the sheets that are selected:

     Dim sh As Object
     For Each sh In ActiveWindow.SelectedSheets
         MsgBox sh.Name
     Next sh

If you want to just specify the first sheet of the group and the last sheet of
the group, you could use:

     Dim FirstIndex As Long
     Dim LastIndex As Long
     Dim Temp As Long

     Dim iCtr As Long

     FirstIndex = Worksheets("Reports==>").Index
     LastIndex = Worksheets("pivots==>").Index

     If LastIndex < FirstIndex Then
         Temp = FirstIndex
         FirstIndex = LastIndex
         LastIndex = Temp
     End If

     For iCtr = FirstIndex To LastIndex
         MsgBox Worksheets(iCtr).Name
     Next iCtr

Hi Dave

Many thanks, what i want to do is loop through the sheets and just
make sure they are grouped (as normally the columns and rows etc are
ungrouped when they're being worked on) before I print them out/send
them to people etc.

Does that make sense?

 

[Dave Peterson]

Yep, it makes sense.

You'll have to add the code to do the grouping to one of those suggestions if
you want to loop through the sheets.



On 06/09/2010 08:23, Stav19 wrote:

 

[GS]

What makes your post confusing is the terminology you're using with
respect to "grouping" sheets vs "grouping" columns/rows via
OutlineLevels. Firstly, if you already have a list of sheetnames that
you need to process outlines for then you don't need to 'group' sheets
to do this particular task. -You can just use a loop to process each
sheet in your list of sheetnames.

Example:
<snip>
Dim avWks As Variant, i As Integer

avWks = Array("Sheet1", "Sheet2", "Sheet3")
For i = LBound(avWks) To UBound(avWks)
Sheets(avWks(i)).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Next
</snip>

If you want to group sheets programmatically so you can do something
with them 'as a group' then here's a reusable procedure that does that
in various ways.

' GroupSheets()
' This procedure requires only the necessary amount of coding be used
' in the Caller. By default, it requires passing only the first arg.
' Use Example: GroupSheets "Sheet1,Sheet3"
' creates a group of only those sheets.
' To group all sheets in a workbook except those sheets:
' GroupSheets "Sheet1,Sheet3", False
' You can pass the Wkb arg to specify any open workbook.
' (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub GroupSheets(sSheetnames As String, _
Optional bInGroup As Boolean = True, _
Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether sSheetnames are to be included
' or excluded. Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

Dim Shts() As String, sz As String
Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

i = 0: If Wkb Is Nothing Then Set Wkb = ActiveWorkbook

For Each wks In Wkb.Worksheets
bNameIsIn = (InStr(sSheetnames, wks.name) > 0)
sz = "" '//init vars
If bInGroup Then
'Include sSheetname in group
If bNameIsIn Then sz = wks.name
Else
'Exclude sSheetname from group
If Not bNameIsIn Then sz = wks.name
End If
'Build the array
If Not sz = "" Then
ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
End If
Next
'Select the array
ActiveWorkbook.Worksheets(Shts).Select
End Sub

To use it:
GroupSheets "Sheet1,Sheet2,Sheet3"

With ActiveWindow.SelectedSheets
'do stuff
End With

HTH

 

[Stav19]

What makes your post confusing is the terminology you're using with
respect to "grouping" sheets vs "grouping" columns/rows via
OutlineLevels. Firstly, if you already have a list of sheetnames that
you need to process outlines for then you don't need to 'group' sheets
to do this particular task. -You can just use a loop to process each
sheet in your list of sheetnames.

Example:
<snip>
  Dim avWks As Variant, i As Integer

  avWks = Array("Sheet1", "Sheet2", "Sheet3")
  For i = LBound(avWks) To UBound(avWks)
    Sheets(avWks(i)).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
  Next
</snip>

If you want to group sheets programmatically so you can do something
with them 'as a group' then here's a reusable procedure that does that
in various ways.

' GroupSheets()
' This procedure requires only the necessary amount of coding be used
' in the Caller. By default, it requires passing only the first arg.
' Use Example:  GroupSheets "Sheet1,Sheet3"
'               creates a group of only those sheets.
' To group all sheets in a workbook except those sheets:
'               GroupSheets "Sheet1,Sheet3", False
' You can pass the Wkb arg to specify any open workbook.
'   (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub GroupSheets(sSheetnames As String, _
                Optional bInGroup As Boolean = True, _
                Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether sSheetnames are to be included
' or excluded. Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

  Dim Shts() As String, sz As String
  Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

  i = 0:  If Wkb Is Nothing Then Set Wkb = ActiveWorkbook

  For Each wks In Wkb.Worksheets
    bNameIsIn = (InStr(sSheetnames, wks.name) > 0)
    sz = "" '//init vars
    If bInGroup Then
      'Include sSheetname in group
      If bNameIsIn Then sz = wks.name
    Else
      'Exclude sSheetname from group
      If Not bNameIsIn Then sz = wks.name
    End If
    'Build the array
    If Not sz = "" Then
      ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
    End If
  Next
  'Select the array
  ActiveWorkbook.Worksheets(Shts).Select
End Sub

To use it:
  GroupSheets "Sheet1,Sheet2,Sheet3"

  With ActiveWindow.SelectedSheets
    'do stuff
  End With

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry

Thanks for your post and apologies for the confusion, what I meant was
grouping rows and columns on each sheet for a range of sheets (so I
guess in this case not a group of sheets, but each sheet - useful to
know how to do it for a group of sheets). I've got it to work by
changing what dave posted me a little, although in all honesty, not
sure how all of it works.

So when i looked at your stuff, it looks awesome, but doesn't mean a
whole much to me, I'll have to have a play to try and understand it.
The main reason I wanted to do the above in a macro was just to get
comfortable with doing things, to then use that in other reports I
use, a lot of the above looks way too advanced for me!

Will post what I've got to work (and more or less understand) tomoro!

Cheers
Pete

 

[Stav19]

Hi Garry

Thanks for your post and apologies for the confusion, what I meant was
grouping rows and columns on each sheet for a range of sheets (so I
guess in this case not a group of sheets, but each sheet - useful to
know how to do it for a group of sheets).  I've got it to work by
changing what dave posted me a little, although in all honesty, not
sure how all of it works.

So when i looked at your stuff, it looks awesome, but doesn't mean a
whole much to me, I'll have to have a play to try and understand it.
The main reason I wanted to do the above in a macro was just to get
comfortable with doing things, to then use that in other reports I
use, a lot of the above looks way too advanced for me!

Will post what I've got to work (and more or less understand) tomoro!

Cheers
Pete- Hide quoted text -

- Show quoted text -

Hi All

Here's the code:

ub Loopdeloop()

Dim FirstIndex As Long
Dim LastIndex As Long
Dim Temp As Long
Dim iCtr As Long
Dim Sh As Worksheet

FirstIndex = Worksheets("Reports==>").Index
LastIndex = Worksheets("pivots==>").Index

If LastIndex < FirstIndex Then
Temp = FirstIndex
FirstIndex = LastIndex
LastIndex = Temp
End If

For iCtr = FirstIndex To LastIndex
Worksheets(iCtr).Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=1
Next iCtr

End Sub

Still not quite sure what the "temp" bit is there for...

Cheers

pete

 

[Dave Peterson]

You didn't say what order your worksheets are in.

Is Reports==> to the right or left of the Pivots==> sheet?

The temp variable allows the code to work no matter which sheet is to the left.

FirstIndex = Worksheets("Reports==>").Index
LastIndex = Worksheets("pivots==>").Index

If LastIndex < FirstIndex Then
Temp = FirstIndex
FirstIndex = LastIndex
LastIndex = Temp
End If

If the sheet indices are 4 (for reports) and 13 (for pivots), then going from 4
to 13 makes sense.

But if the sheet indices are 13 for reports and 4 for pivots, then going from 13
to 4 won't don anything.

So the temp variable is used to swap these indices.

Try using something like this without the temp variable:

firstindex = lastindex
lastindex = firstindex

You'll see the problem.

 

[GS]

Hi All

Here's the code:

ub Loopdeloop()

Dim FirstIndex As Long
Dim LastIndex As Long
Dim Temp As Long
Dim iCtr As Long
Dim Sh As Worksheet

FirstIndex = Worksheets("Reports==>").Index
LastIndex = Worksheets("pivots==>").Index

If LastIndex < FirstIndex Then
Temp = FirstIndex
FirstIndex = LastIndex
LastIndex = Temp
End If

For iCtr = FirstIndex To LastIndex
Worksheets(iCtr).Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=1
Next iCtr

End Sub

Still not quite sure what the "temp" bit is there for...

Cheers

pete

I don't see why you're selecting the sheets since it's not necessary to
do that once you have a ref to each sheet. I also don't know why you
have to use two lines to set the outline levels. Seems like you're
using the macro recorder's code rather than practicing efficient
programming.

Also, I can't understand why the sheets need to be indexed. If you put
their names into an array then just do that in the correct order.

Still suggesting this is faster and more efficient:
<snip>
Dim avWks As Variant, i As Integer

avWks = Array("Sheet1", "Sheet2", "Sheet3")
For i = LBound(avWks) To UBound(avWks)
Sheets(avWks(i)).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Next
</snip>

 

[Dave Peterson]

I don't have to have to know the names of each sheet between "pivots==>" and
"reports==>".

I can use the .index to find all the sheets between them. This makes it easier
to insert new sheets, rename existing sheets, delete sheets or even move
existing sheets in and out of that "sandwich" formed by the pivots and reports
worksheets.

 

[GS]

Dave Peterson expressed precisely :

I don't have to have to know the names of each sheet between "pivots==>" and
"reports==>".

I can use the .index to find all the sheets between them. This makes it
easier to insert new sheets, rename existing sheets, delete sheets or even
move existing sheets in and out of that "sandwich" formed by the pivots and
reports worksheets.

Ah! Very clever technique. I'll make note of that and your sample.
Thanks!