Date command in Excel headers

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

How do I enter a date command in a header that displays only the current year
and not the complete date?
 
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?
 
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!
 
You have to use a workbook macro.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = Format(Date, "yyyy")
End Sub
 
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
 
Ok, so where do I put this macro?

KC said:
You have to use a workbook macro.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = Format(Date, "yyyy")
End Sub
Click to expand...
 
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

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
 
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
 
Back
Top