Putting Text from a cell into the Center Header

The easiest method of inserting cell references in the header is as follows:

• Insert a row at the top of the worksheet
• Merge the cells in the top row for the width of the printout.
• Enter a link to the required cells. (Insert equals sign and then go to Sheet5 and click the merged cells E3/F3)
• You can centre, left or right justify content of the merged cells for the header.
• In page setup, if required, set the first row as "Rows to repeat"
• You can if you wish go into page setup and decrease the size of the top margin so the inserted row prints higher on the sheet.

Sub Sheet5_Cell_In_AllHeaders()
Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Sheets
       WS.PageSetup.CenterHeader = Sheets(5).Range("E3").Value
    Next
End Sub

Gord

If you really want to use VBA code then I suggest some modification to Gord Dibben's code.

Firstly insert the code as Before Print Event in the ThisWorkbook Module (Double click ThisWorkbook in the Project Explorer in the left column of the VBA editor). This will ensure that the Header is always updated before printing if the value in the reference cell is changed.

If inserting as Event code in ThisWorkbook then do not alter the sub name.

Optional but the following example includes code to be selective about which sheets to include the page header. Using Select Case allows you to list the sheets to be included as per the example.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Sheets
        Select Case WS.Name
            Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
                WS.PageSetup.CenterHeader = Sheets("Sheet5").Range("E3").Value
        End Select
    Next
End Sub