Same footer for worksheets (grouped)

[Jan]

I have a workbook with several spreadsheets, which I will be using each month
as a template. I recorded a macro with the hopes of grouping the worksheets
and then selecting the page setup to create the same footer information,
"Date prepared: & Date", on each worksheet. Although the macro groups the
worksheets, it only puts the footer information on the Summary worksheet.
Below is the code. Can anyone help me revise the code to put the footer on
each worksheet.

Sub GroupFooter()
'
' GroupFooter Macro
' Macro recorded 12/21/2006
Sheets(Array("Summary", "IFS Corp", "DASHIELL", "DACON", "MJELECTRIC", "BP",
"IUS", _
"ITS")).Select
Sheets("Summary").Activate 'I think this is part of the problem
With ActiveSheet.PageSetup
..LeftHeader = ""
..CenterHeader = "&G"
..RightHeader = ""
..LeftFooter = ""
..CenterFooter = ""
..RightFooter = "&""Arial,Italic""&8Date prepared: &D"
..LeftMargin = Application.InchesToPoints(0.5)
..RightMargin = Application.InchesToPoints(0.5)
..TopMargin = Application.InchesToPoints(1.1)
..BottomMargin = Application.InchesToPoints(1)
..HeaderMargin = Application.InchesToPoints(0.5)
..FooterMargin = Application.InchesToPoints(0.5)
..PrintHeadings = False
..PrintGridlines = False
..PrintComments = xlPrintNoComments
..PrintQuality = 600
..CenterHorizontally = True
..CenterVertically = False
..Orientation = xlPortrait
..Draft = False
..PaperSize = xlPaperLetter
..FirstPageNumber = xlAutomatic
..Order = xlDownThenOver
..BlackAndWhite = False
..Zoom = False
..FitToPagesWide = 1
..FitToPagesTall = False
..PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

TIA
Jan

 

[Tom Ogilvy]

Sub GroupFooter()
'
' GroupFooter Macro
' Macro recorded 12/21/2006
Sheets(Array("Summary", "IFS Corp", "DASHIELL", "DACON", "MJELECTRIC", "BP",
"IUS", _
"ITS")).Select
for each sh in ActiveWindow.SelectedSheets
With sh.PageSetup
..LeftHeader = ""
..CenterHeader = "&G"
..RightHeader = ""
..LeftFooter = ""
..CenterFooter = ""
..RightFooter = "&""Arial,Italic""&8Date prepared: &D"
..LeftMargin = Application.InchesToPoints(0.5)
..RightMargin = Application.InchesToPoints(0.5)
..TopMargin = Application.InchesToPoints(1.1)
..BottomMargin = Application.InchesToPoints(1)
..HeaderMargin = Application.InchesToPoints(0.5)
..FooterMargin = Application.InchesToPoints(0.5)
..PrintHeadings = False
..PrintGridlines = False
..PrintComments = xlPrintNoComments
..PrintQuality = 600
..CenterHorizontally = True
..CenterVertically = False
..Orientation = xlPortrait
..Draft = False
..PaperSize = xlPaperLetter
..FirstPageNumber = xlAutomatic
..Order = xlDownThenOver
..BlackAndWhite = False
..Zoom = False
..FitToPagesWide = 1
..FitToPagesTall = False
..PrintErrors = xlPrintErrorsDisplayed
End With
Next
End Sub

You need to minimixe the number of properties you set as each one will take
a measurable amount of time. Most of these are default values, so only
change what you need.
example
..LeftHeader = ""

not needed.

 

[Gary Keramidas]

hi tom:

i noticed your comment at the bottom about some items that are not needed. i got
bit by this.

in one report i set a left header with the date date(). on another report, i
didn't have a left header property. the next day, the report with no left header
printed the left header with the prior's days date, even though i had not set a
left header property.

now, if i set a property somewhere on some report, i always set that property to
"" on every report, even when it's not used.

your comments are welcome.

 

[Tom Ogilvy]

If you don't know that you don't need it, by all means.

 

[Jan]

Tom,

Thanks that did it. However, I found it odd that the following line of code
regarding font format (Arial, Italic), RightFooter = "&""Arial,Italic""&8Date
prepared: &D"
, was only applied to the first worksheet. All the other worksheets
reverted back to the default for the font format.

Jan