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.

On 06/10/2010 03:09, Stav19 wrote:
On Jun 9, 10:46 pm, wrote:
On Jun 9, 4:44 pm, wrote:





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- 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