0

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.

  • 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. – Ron Rosenfeld Feb 8 '18 at 14:08
0

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.

  • Peter, I'll definitely try that out! I assume this wouldn't work if the file is primarily used in the online Excel app? – rtinstruct Feb 8 '18 at 15:23
  • @rtinstruct let me know how you get on with it, as for online im not sure as ive never used online services for excel – PeterH Feb 8 '18 at 15:25
  • 1
    Why not obtain the sheet name directly using native VBA methods? – Ron Rosenfeld Feb 8 '18 at 17:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.