Date command in Excel headers

[Doug]

How do I enter a date command in a header that displays only the current year
and not the complete date?

 

[Jim Thomlinson]

So the value in the header is 2010 or is it 1/1/2010 formatted to show only
the year? If it is only the year then what date were you hoping for. What
formula did you want to use against the date?

 

[Doug]

Jim, I want to insert only the year, but I want it to update with each new
year. I use the same spreadsheet from year to year and I don't want to have
to manually put the year in the header for every month and every year. I
would like a command or formula that only inserts the year (2010) and not the
complete date (1/1/2010) like the "Date" button in Excel. This wa I don't
have to edit it each year.

Thanks!

 

[KC hotmail com>]

You have to use a workbook macro.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = Format(Date, "yyyy")
End Sub

 

[Gord Dibben]

You mean a header as in Footers and Headers for print setup?

Sub YearInFooter()
ActiveSheet.PageSetup.RightFooter = Year(Now)
End Sub

For all sheets in workbook.

Sub Date_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each WS In wkbktodo.Worksheets
WS.PageSetup.RightFooter = Year(Now)
Next
End Sub


Gord Dibben MS Excel MVP

 

[Doug]

Ok, so where do I put this macro?

You have to use a workbook macro.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = Format(Date, "yyyy")
End Sub

 

[Doug]

Yes, I mean a header as in Footers and Headers for print setup. Ok, so where
do I put this macro to make it work. Also, if I want the date to appear as a
left header instead of a right footer do I simply change "RightFooter" to
"LeftHeader"?

Thanks!

 

[Doug]

Nevermind, I found it.

Ok, so where do I put this macro?

 

[Doug]

Nevermind, I found it and answered my own question.

Thanks!

 

[Gord Dibben]

Doug

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Doug

See my reply about macros and where to place them and run them.

KC has posted event code which goes into Thisworkbook Module which is a
different module than a General module.

When you get into the VBE double-click on Thisworkbook to open the module.

Paste KC's code in there. It will run automatically when you print.

BTW.............yes, change rightfooter to leftheader.


Gord Dibben MS Excel MVP