Automatically change header

[Rita Palazzi]

Win XP Professional
Office 2000

I have multiple worksheets, each with it's own header. The only common
item in all headers is the date (month and year). Is there a way to
programatically change the date in all headers without having to edit
the header of every worksheet?

Thanks in advance for any help you can provide!

Rita Palazzi
Senior Engineer / Global Trade Services
FedEx Express

 

[LB79]

Put &[Date] in the header - this will update according to the P
date

 

[Rita Palazzi]

I need the previous month in the format of mmmm yyyy i.e August 2004.
Any other suggestions?

Thanks
Rita

 

[Earl Kiosterud]

Rita,

I think a macro will have to set the date into the headers. The &[Date]
code causes the date of printing to appear in the header or footer, but I
don't think the format can be changed. If you select all the sheets and
mess with Page Setup, it will put the active sheet's page setup info into
all the sheets -- not desirable since your sheets have individual headers.
If you're willing to put in a macro, we can write it for you. Do you want
the date printed (as with the &[Date] code), or a fixed date?

 

[Rita Palazzi]

I don't mind using a macro. I have a set of charts for each region of
the world on each sheet. For example, I have 4 APAC charts on one sheet
(prints one page), four Europe charts on one sheet, etc. The header
identifies the region centered on the top line and the Month/year the
charts are for. Example:

APAC Import Performance
August 2004


If is is helpful, I have a chart data sheet that contains the month and
each region name, so if I need top use this as a reference in the macro,
that's cool. I just don't know enough about VB to write the macro
myself. Your help is INVALUABLE!!

Rita

 

[Earl Kiosterud]

Rita,

It isn't clear to me where to get the date from each sheet (that was your
original question - how to put a date into the page header). Also, if each
sheet requires only one page to print, you could as easily build your header
into the sheet (first couple of rows or so), and let it print the sheet
straightaway. There appears to be no particular utility in the header in
this case.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I don't mind using a macro. I have a set of charts for each region of
the world on each sheet. For example, I have 4 APAC charts on one sheet
(prints one page), four Europe charts on one sheet, etc. The header
identifies the region centered on the top line and the Month/year the
charts are for. Example:

APAC Import Performance
August 2004


If is is helpful, I have a chart data sheet that contains the month and
each region name, so if I need top use this as a reference in the macro,
that's cool. I just don't know enough about VB to write the macro
myself. Your help is INVALUABLE!!

Rita

Rita,

I think a macro will have to set the date into the headers. The &[Date]
code causes the date of printing to appear in the header or footer, but I
don't think the format can be changed. If you select all the sheets and
mess with Page Setup, it will put the active sheet's page setup info into
all the sheets -- not desirable since your sheets have individual headers.
If you're willing to put in a macro, we can write it for you. Do you want
the date printed (as with the &[Date] code), or a fixed date?

 

[Rita Palazzi]

Just an FYI on the date... we have a large worksheet where we have about
3 years of weekly data input. I got REAL tired of changing my cell
references on graphs every week (got about 25 graphs in one workbook,
some showing up to 5 indices), so I built what I call a "chart data
sheet". Once the data is input, the user goes to the chart data sheet
and only changes one date at the top. All cells on this sheet use the
VLOOKUP function based on the input date to reference the data entry
sheet. When the date is changed, every cell on this sheet automatically
changes and every single graph in the workbook automatically updates.
Pretty cool! Anyway, I was hoping to use the cell in which the user
enters the date on the chart data sheet as the source and was hoping you
could reference that cell within the header area itself. If you don't
think I can reference a cell within the header, I'll probably just take
your suggestion and build the header into the cell/page area. Just
trying to avoid that if I can. Let me know your thoughts...

Thanks a bunch for your time!
Rita

 

[Gord Dibben]

Rita

Sub CellInFooter()
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").Value
End With
End Sub

Is this what you're looking for?

Gord Dibben Excel MVP