"grouped" footer changes

[Guest]

Recorded a macro to insert/change footer info.
Works great, while recording.
Attached macro to icon on toolbar.
Opened another file, clicked on macro.
It seemed to be working at something, but when I went
to check each sheet, only the first sheet was changed.
Tried this whole process 3 times - Same result each time.
Q1: What may be causing this?
Q2: I'm looking to insert Contract Number, found in cell E4
of Sheet c1, into CentreFooter.
How can this be done?

The following shows the pertinent parts of the recorded macro:
Option Explicit
Sub ContractFooter()
' ContractFooter Macro
'
Sheets(Array("c1", "Options", "Pricing", "Notes", "Warranty_CDN",
"Warranty_USA", _
"General Info")).Select
Sheets("c1").Activate
With ActiveSheet.PageSetup
' The following date will be manually adjusted 3 or 4 times per year.
.LeftFooter = "&""CG Times (W1),Bold""&8Feb. 01, 2006"
' Looking to put Contract # in CenterFooter (from cell E4 of sheet c1)
.CenterFooter = ""
.RightFooter = "&""CG Times (W1),Bold""&8&F &A"
.FooterMargin = Application.InchesToPoints(0.25)
End With
' The following selection "ungroups" the array
Sheets("c1").Select
End Sub

Thanks in advance for any help and direction.

 

[Dave Peterson]

Some things you have to loop through:

Option Explicit

Sub ContractFooter()
Dim mySheetNames As Variant
Dim iCtr As Long

mySheetNames = Array("c1", "Options", "Pricing", "Notes", "Warranty_CDN", _
"Warranty_USA", "General Info")

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(iCtr)).PageSetup
' The following date will be manually adjusted 3 or 4 times per year.
.LeftFooter = "&""CG Times (W1),Bold""&8Feb. 01, 2006"
' Looking to put Contract # in CenterFooter (from cell E4 of sheet c1)
.CenterFooter = ""
.RightFooter = "&""CG Times (W1),Bold""&8&F &A"
.FooterMargin = Application.InchesToPoints(0.25)
End With
Next iCtr

End Sub

 

[Guest]

Right On!!
Bonus: Now I can specify which pages this macro should operate on.
I don't need the procedure on each page.
I'll re-post Question # 2 (Q2), as I can best have this done within a
different
macro which exists already.

Thanks muchly for your super fast response.

 

[Dave Peterson]

Page setup is for all the worksheet--not per physical page per worksheet.

Excel doesn't work the same way as MSWord does with a different header/footer
per section.

But Ron de Bruin has some sample code that could work for you. (He applies the
header/footer, prints one page, then changes it and prints the rest.)

http://www.rondebruin.nl/print.htm#Header

J.E. McGimpsey has a version here, too:
http://mcgimpsey.com/excel/subs/firstfooter.html

 

[Guest]

Dave:
I should write as carefully as you read.
What you gave me is Exactly what I need.
What I should have written was: Now I can specify which worksheet
(not pages), require the footer.
I will make a note of the additonal info for future reference.
Thanks much.

 

[Dave Peterson]

Glad you got what you wanted.

Dave:
I should write as carefully as you read.
What you gave me is Exactly what I need.
What I should have written was: Now I can specify which worksheet
(not pages), require the footer.
I will make a note of the additonal info for future reference.
Thanks much.