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

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

posted first in Excel Charts

You could use programming to add a footer to all the charts and chart
sheets. For example:

'========================
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
.CenterFooter = "DRAFT COPY"
.RightFooter = "Page &P"
End With
Next chObj
Next ws

For Each ch In ActiveWorkbook.Charts
With ch.PageSetup
.CenterFooter = "DRAFT COPY"
.RightFooter = "Page &P"
End With
Next ch

End Sub
'============================

Ches wrote:
> 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
>
> posted first in Excel Charts


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks, Debra, with your advice I did the following, which changes the
header or footer on all charts - just what I wanted!

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?

You're welcome, and thanks for posting your solution. You could combine
the two macros into one, and it might be slightly faster to run. I've
changed the date in the following code, so it will print the long date
format that you want.

'===================
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
'=====================

Ches wrote:
> Thanks, Debra, with your advice I did the following, which changes the
> header or footer on all charts - just what I wanted!
>
> 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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Perfect, Debra!

I don't want to be greedy, but can I add the day of the week to the date
string (as in Monday, July 4, 2005)?

Thanks so much for your help, I've learned a lot - I've always kept away
from the programming, and can see what I've been missing! I need to study up
on this, from the begining...

You're welcome! My Long Date format includes the day of the week, but
since yours doesn't, you can specify the exact format that you want, e.g.:

.RightFooter = "&8" & "Printed &T on " _
& Format(Date, "dddd, mmm dd, yyyy")


Ches wrote:
> Perfect, Debra!
>
> I don't want to be greedy, but can I add the day of the week to the date
> string (as in Monday, July 4, 2005)?
>
> Thanks so much for your help, I've learned a lot - I've always kept away
> from the programming, and can see what I've been missing! I need to study up
> on this, from the begining...


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html