Hi Mark,
I would break this up so that PageSetup is a reusable procedure that receives
an array (or delimited list) of sheet names to act upon. You could also 'Group'
sheets and do PageSetup on the group (selected sheets). Here's some food for
thought...
Option Explicit
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
' 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
' To group all sheets in a workbook pass an empty string:
' GroupSheets "", False
' You can pass the Wkb arg to specify any open workbook.
' (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
Public Sub GroupSheets(Sheetnames As String, _
Optional bInGroup As Boolean = True, _
Optional wkb As Workbook)
' Groups sheets in Wkb based on whether Sheetnames
' are to be included or excluded in the grouping.
' 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
If wkb Is Nothing Then Set wkb = ActiveWorkbook
For Each wks In wkb.Worksheets
bNameIsIn = (InStr(Sheetnames, wks.name) 0)
If bInGroup Then
If bNameIsIn Then sz = wks.name
Else
If bNameIsIn Then sz = "" Else sz = wks.name
End If
If Not sz = "" Then '//build the array
ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
End If
Next
wkb.Worksheets(Shts).Select
End Sub 'GroupSheets
Sub TestGroupSheets()
Dim wks As Worksheet
GroupSheets "Sheet1,Sheet2,Sheet3"
For Each wks In ActiveWindow.SelectedSheets
wks.Activate: ActiveWindow.DisplayOutline = False
Next wks
End Sub
Sub TestGroupSheets2()
Dim avWks As Variant
avWks = Array("Sheet1", "Sheet2", "Sheet3")
Dim i As Integer
For i = LBound(avWks) To UBound(avWks)
Sheets(avWks(i)).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Next
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion