Same Header/Footer on multiple sheets
Posted by Keiser on January 23, 2002 8:48 AM
I´ve got a problem!
Does anyone know if it is possible to put the same header/footer on multiple sheets (datasheets and chartsheets) at the same time?
I´ve got 3 sheets with data on it and I want to put the same header on them in one action witch is done so.
Select the first sheet press/hold shift and select the last sheet. View header/footer and define them, "Presto" same header/footer on them all.
Ok here´s the problem.
When I add a chart sheet to this selection and do the for-mentioned aproach it doesn´t work!!!. I´ve try´ed recording two macro´s in witch both do the same thing, that is one put´s header/footer on a regular sheet and the other on the chart sheet. Now, looking at the code it isn´t the same, thus making this impossible, or what???
p.s.
I can´t use soulutions that use templates,macros or VBA
Posted by Damon Ostrander on January 23, 2002 9:42 AM
Hi Keiser,
No, if you can't use a VBA macro you will have to enter the headers/footers on the Chart sheets and Worksheets separately (and this applies also to Dialog sheets, if you have them). Fortunately, this means that you only have to enter the headers and footers twice, as you can do all the worksheets at once, and all the Chart sheets at once.
Damon
Posted by Darren on January 24, 2002 2:08 AM
I have a similar problem. I have a workbook with multiple sheets, on all of which I want the same header and footer. If I select all sheets and set the page up as desired this is fine. However, if I try to record a macro to do this only the first sheet is set up properly. Any thoughts on this??
Posted by Damon Ostrander on January 24, 2002 2:26 PM
Hi Darren,
Yes, it appears you must set the Headers and Footers one sheet at a time. The For..Each loop makes this easy, though a but slow (setting the PageSetup object's properties is slow because each property causes Excel to poll the printer, which if it is a network printer takes time).
Here's how to do it:
Dim Sh As Sheet
For Each Sh In Worksheets(Array("Sheet1", "Sheet2"))
With Sh.PageSetup
.LeftHeader = ""
.CenterHeader = "My New Header"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "My New Footer"
.RightFooter = ""
End With
Next Sh
Damon
PS. If you want to do all Worksheets, change the Worksheets(Array("Sheet1", "Sheet2")) to just Worksheets, or for all Chart sheets to just Charts.
