How can I update an Excel Header or Footer with a Cell Value?
MS Excel does not appear to be able to interpret a cell value inside any Page Header or Footer. Is there some sort of intelligent or automated way to update the Header or Footer with the value of the cell shown in the header?
Yes there is, albeit it is only possible via VBA (Visual Basic for Applications) code in Excel Also once that code is placed inside the Workbook, it can be made available for each Worksheet. However the XLSX must then be saved as an Excel Macro-Enabled-Workbook (*.xlsm). This is how you can achieve this.
SET UP VBA CODE:
1. In Excel press Alt + F11 to enter the VBE interface.
6. Paste code from the 'yellow' window below, into the right Module pane.
Copy the code below from " Option Explicit " to " End Function " and paste it into your Module window. Make changes based on the notes/comments (in green) provided below.
' Add a calculated cell (A1000) on each sheet in this workbook. When you run the MACRO "AddHeaderToAll_FromEachSheet",
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Function HdrFtr(sText As String, Optional ByVal sFont As String, Optional iColor As Long, Optional iFontSize As Long) As String
If Len(sFont) Then sFont = "&""" & sFont & """"
If Abs(iFontSize) Then sFontSize = "&" & Abs(iFontSize)
If iColor <> 0 Then
HdrFtr = sFont & sFontSize & sColor & sText
7. Your screen is shown below (after the 'paste' is complete).
8. Click the SAVE icon on the toolbar and then press Alt + Q to close the VBA interface.
9. Click menu File/Save As to save the Workbook before any other changes to an Excel-Macro-Enabled Workbook (*.xlsm) so it will preserve the VBA code
TEST THE CODE:
1. Enter some calculated data in cell A1000 (on each sheet)
Always run the Macro Before Printing to PDFeXPLODE Printer
You can change LeftHeader ( on the line ws.PageSetup.LeftHeader = in the code - ' AddHeaderToAll_FromEachSheet' shown above) to:
RightHeader or CenterHeader or LeftFooter or CenterFooter or RightFooter
You can change font color in this line : HdrFtr(ws.Range("A1000").Value, "Arial,Regular", vbBlack, 7)
vbBlack, vbBlue , vbWhite, VbRed
You can change font size from '7' to any size, for example '30'
The reference to cell A1000 can be changed to any other cell of your choice. Just modify this line : HdrFtr(ws.Range("A1000").Value, "Arial,Regular", vbBlack, 7)
If you are looking to create a PDF-eXPLODE Tag in an Excel header or Footer, please see How do I use PDF-eXPLODE with Microsoft Excel?
This code is set up as a Macro within Excel. Depending on your security setting for Macros, this Macro may not run at all. Contact your IT personnel if unsure. Here is where you can check your Macro settings: In Excel, click on the menu File / Option / Trust Center . Next click on the button TRUST CENTER SETTINGS.... Next click on the Macro Settings menu item on the left pane:
Select the appropriate option as above.
|Posted - Thu, Jan 17, 2019 at 2:25 PM. This article has been viewed 6083 times.|
|Online URL: https://kb.pdf-explode.com/article/how-can-i-update-an-excel-header-or-footer-with-a-cell-value-115.html|
Powered by PHPKB (Knowledge Base Software)