Printing mulitple charts with same header and footer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ches - 01 Jul 2005 16:29 GMT

I am using Excel 2000/Windows2KSP4, and Excel2003/WinXPSP1.

I have a large workbook, with multiple sheets, each containing several
charts. I'm looking for an easy/simple way to give each chart the same header
and footer info (File path, prepared by, and tab). I've tried grouping the
charts (using shift-select), but while this groups them, the header/footer
changes apply to one chart only. Grouping all hte worksheets gives them the
same header footer, but not hte charts contained on them.

Has anyone any ideas how to do this easily?

Possibly setting each chart on its own worksheet for printing might be a
solution (creat chart as new sheet) (it too didn't work when I tried it); but
I wanted the chart on the same sheet as the data.

TIA for any bright ideas...

Reply to this Message

David McRitchie - 03 Jul 2005 17:44 GMT

You could group the sheets and run a macro that works on the
headers footers for each sheet in the group. If it is always the same
it would be safer to just use the name of the active sheet and a sheetname
derived from that name and changer their headers and footers..

look for use of Grouped Sheets in
http://www.mvps.org/dmcritchie/excel/pathname.htm#beforeprint
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> I am using Excel 2000/Windows2KSP4, and Excel2003/WinXPSP1.
>
[quoted text clipped - 12 lines]
>
> TIA for any bright ideas...

Reply to this Message

Ches - 04 Jul 2005 17:46 GMT

Thanks Dave, but grouping the sheets didn't group the charts, and grouping
the charts on a sheet didn't work either, I'd tried both before posting.
Thanks to Debra Dalgleish, we worked out the following which will change
header and footer (with appropriate editing for yourname and yourdate)

==================================================
Sub ChartFootersHeaders()

Dim ws As Worksheet
Dim chObj As ChartObject
Dim ch As Chart

For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
With chObj.Chart.PageSetup
.LeftFooter = "&8" & "Prepared by yourname yourdate"
.RightFooter = "&8" & "Printed &T on " _
& Format(Date, "Long Date")
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next chObj
Next ws

For Each ch In ActiveWorkbook.Charts
With ch.PageSetup
.LeftFooter = "&8" & "Prepared by yourname yourdate"
.RightFooter = "&8" & "Printed &T on " _
& Format(Date, "Long Date")
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next ch

End Sub
=======================================

Reply to this Message

Rate this thread: