How do I add only the current month and year to the header of an Excel workbook?

Question

I need to input the current year into my header and add the sheet name as the day (e.g., 2/[Tab]/[YYYY]). Is this possible? I know the Insert Date option puts a dynamic date into the header, but I need to be able to control the month manually if possible.

Any suggestions would be greatly appreciated.

Solution

It is possible to do it using VBA.

First Enter the below formula into CELL A1, this can be changed to any cell you want, just ensure you change the reference point in the macro part.

=DAY(TODAY())&"/"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"/"&YEAR(TODAY())

It will create your dynamic date and sheet name combo.

Then put the below into a module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

It will take the date combo and set it into your header before printing.

It should also come into effect before you view the print preview.

Comments

[Ron Rosenfeld]

One way is to use a VBA routine and you can set the header sections however you wish. The macro could be either run manually, or be triggered by a Workbook_BeforePrint event, depending on your requirements.

[rtinstruct]

Peter, I'll definitely try that out! I assume this wouldn't work if the file is primarily used in the online Excel app?

[PeterH]

@rtinstruct let me know how you get on with it, as for online im not sure as ive never used online services for excel

[Ron Rosenfeld]

Why not obtain the sheet name directly using native VBA methods?