Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]() 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? |
#4
![]() |
|||
|
|||
![]()
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 |
#5
![]() |
|||
|
|||
![]()
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... |
#6
![]() |
|||
|
|||
![]()
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 |
#7
![]() |
|||
|
|||
![]()
Thanks again Debra, I thought it might be that simple, but after quite a few
tries to get it, I'd given up and asked! I haven't managed to set up my longdate format this way, (WinXP) although I'd like to; how do you do this? p.s. any suggestions for how to go about studying up on VB, from the begining, will be much appreciated. |
#8
![]() |
|||
|
|||
![]()
I found the WinXP location, in "Customise Regional Settings"
Thanks for all your help, Debra. Ches, in London, ON. p.s. any suggestions for how to go about studying up on VB, from the begining, will be much appreciated. |
#9
![]() |
|||
|
|||
![]()
Thanks for letting me know that you found the date setting.
For online information on VBA, see David McRitchie's list of tutorials http://www.mvps.org/dmcritchie/excel...m#vbatutorials For books, there's a list on my web site: http://www.contextures.com/xlbooks.html John Walkenbach's books are good, and so is Microsoft Excel 2002 Visual Basic for Applications Step by Step by Reed Jacobson. If possible, browse through a few at the bookstore, and see which style suits you. And it's always nice to help a neighbour <g -- I'm in Mississagua. Ches wrote: I found the WinXP location, in "Customise Regional Settings" Thanks for all your help, Debra. Ches, in London, ON. p.s. any suggestions for how to go about studying up on VB, from the begining, will be much appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Print all charts in a workbook (multiple worksheets) | Charts and Charting in Excel | |||
Give multiple charts on a worksheet/workbook same header or footer | Charts and Charting in Excel | |||
multiple pie of pie charts | Charts and Charting in Excel | |||
Multiple charts in ChartSpace; problems with double Categories | Charts and Charting in Excel | |||
Macro for multiple charts | Excel Worksheet Functions |