Give multiple charts on a worksheet/workbook same header or footer

[Guest]

Using Excel 2003, SP1

I'm creating many charts throughout a large workbook that has many
worksheets, and there are several charts on each worksheet. It is a pain to
set the header and footer individually for each chart.

Can I group these charts together (possibly only in the active worksheet at
any one time) to give them all the same header and footer, as I can group
worksheets together in a workbook, to give them all the same header and
footer?

TIA

 

[Tushar Mehta]

Have you tried it? If it didn't work, you might consider recording a
macro to do the needful. Select a chart. Then, record a macro with
Tools | Macro > Record new macro... Do whatever you want with the
chart, then turn off the macro recorder. Now, select any other chart
and run the macro (Alt+F8 or Tools | Macro > Macros...)

A cautionary note. I vaguely recall a discussion that macros that
change the print setup run very slowly and there may also be a memory
leak that causes XL to run increasingly slowly as the code is repeated.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

I've built on all the advice received to date to construct the following
macro, which changes the header or footer on all charts (special thanks to
Debra Dalgleish for the basis)

footer:
==========================================
Sub ChartFooters()

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 &D"
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 &D"
End With
Next ch

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

and for the headers
===================================================



Sub ChartHeaders()

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
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next chObj
Next ws

For Each ch In ActiveWorkbook.Charts
With ch.PageSetup
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next ch

End Sub

======================================================


Next question: how can I print date in long form (i.e. dddd, mmm dd, yyyy)
in place of &D which returns 04/07/2005?