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

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

On 06/09/2010 07:12, Stav19 wrote:
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

On Jun 9, 2:08*pm, Dave Peterson wrote:
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

On 06/09/2010 07:12, Stav19 wrote:





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- Hide quoted text -

- Show quoted text -

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?

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

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

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 9, 4:44*pm, GS 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

On Jun 9, 10:46*pm, Stav19 wrote:
On Jun 9, 4:44*pm, GS 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

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

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

--
Garry

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

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.



On 06/10/2010 14:07, GS wrote:
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

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.



On 06/10/2010 14:07, GS wrote:
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

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

--
Garry

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