Header/Footer: What can be entered there?

Excel offers the following built-in headers and footers. They can be added to your worksheet easily, with a single mouse click.

However, we're not limited to these options. In fact, you can enter any text/string into your worksheet cell and add it to any part of the header or footer with just one line of VBA code of a macro. E.g., this line will add contents of cell B2 to the left part of a header:

ActiveSheet.PageSetup.LeftHeader=Range("B2").Value

Such a line can be included in the VBA code I'm presenting at the bottom of this post.

The built-in header/footer options allow us to create e.g. such a footer (at the bottom of the snippet):

The Left section contains edited cell value with red one-liner text. The Center section contains a properly sized picture (it could be e.g. a company logo). And the Right section contains another edited cell value, but in multi-line format and using different font type. The height of the footer/header can be easily changed in the page setup, as required, to fit the text length and the picture size.

It's worth to remember that to show the whole length of text you may need to divide it into lines by pressing ENTER at the end of each part of the text. And, if text includes ampersand (&) you must add another one (&&) to display one in the header/footer.

That's fine, but what if we need to add some specific different values to headers or/and footers in each of the pages to be printed? Well, it is possible, with the help of a macro like in this example:

Private Workbook_BeforePrint (Cancel As Boolean)
‘This event-driven macro will enter different header/footer (as encoded
‘below by the user), based on selected relevant worksheet/workbook cells,
‘on each printed worksheet page.
Dim pgs As Integer
Dim topLeft As String
Dim PgArea As String
Dim i As Integer
Dim pgRows As Integer
pgs = ActiveSheet.HPageBreaks.Count
topLeft = "$A$1"    'Change this setting, if needed
pgRows = 50           'Pagebreak set every 50 rows; change as needed
For i = 1 To pgs
    If i > 1 Then topLeft = "$A$" & pgRows * (i - 1) + 1
    PgArea = topLeft & ":$I$" & pgRows * i     'Change col. "I" to whatever is needed
    ActiveSheet.PageSetup.PrintArea = PgArea
‘   Cell from 1^st row in col. A of the printed page is selected in the following line
    ActiveSheet.PageSetup.RightFooter = Cells(pgRows *( i-1)+1, 1)       
    ActiveSheet.PrintOut copies:=1, collate:=True, ignoreprintareas:=False
Next i
End Sub

As commented above, the macro places specified cell value from column A on each printed sheet whenever the user initiates worksheet printing. Obviously, it must be modified to fit user's needs, but it's not that difficult.

And in a simple case, like e.g. to add the date and time to the left footer just before your workbook is printed, you could use the macro like this one:

Private Sub Workbook_BeforePrint(Cancel As Boolean)  
 'This macro will insert current date and time on every printed page 
Dim wsh As Worksheet
For Each wsh In ThisWorkbook.Worksheets
wsh.PageSetup.LeftFooter = "Printed on: " & Format(Now, "dd-mmm-yyyy hh:mm")
Next wsh
End Sub

Possibilities are unlimited...