When you want to get fancy with Headers and Footers in Excel you can use a BeforePrint macro to present the user with a dialog box that will accept a user-entered string to replace the current header.
For example: I have a workbook maintained by others for staffing on various projects. The sheet is issued more or less monthly. When I print it, I want it to say “Project Staffing for MMMMM” where “MMMMM” is the month in question. (I don’t want it to be a simple [&Date] value, which would be “today’s date”.) And in the past I have ALWAYS forgotten to update the header, so I end up wasting the first print, because it shows an outdated header.
So my current Workbook BeforePrint macro looks something like (this is VERY quick and dirty, and Fluther doesn’t allow the indents to make the code look the least bit pretty):
Private Sub Workbook_BeforePrint(Cancel As Boolean)
MyHdr = InputBox(“Enter the Report’s Month”, “Report Month Input”, _
Application.Text(Now(), “Mmmm”))
’ The “Application.Text(Now(), “Mmmm”)) presents “This Month” spelled out, in case that’s
’ really what I want (it’s always possible). (These are comment lines you could leave in place.)
With ActiveSheet.PageSetup
.LeftHeader = “My Report for ” & MyHdr
End With
End Sub
If you really want Excel advice, I strongly recommend MrExcel.com, which has much more knowledgeable experts than me (but it also has me) and much better tools for asking and displaying the information you need.