Home

Add the data from a cell into the header

%3CLINGO-SUB%20id%3D%22lingo-sub-767750%22%20slang%3D%22en-US%22%3EAdd%20the%20data%20from%20a%20cell%20into%20the%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-767750%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20use%20the%20content%20of%20cell%20A1%2C%20for%20example%2C%20as%20the%20recurring%20data%20in%20the%20worksheet's%20header.%26nbsp%3B%20Can%20%23Excel%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-767750%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-768011%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20the%20data%20from%20a%20cell%20into%20the%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-768011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380160%22%20target%3D%22_blank%22%3E%40pda3476%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EYou%20cannot%20reference%20a%20cell%20in%20the%20Header%20section%20(Say%20by%20typing%20%3DC5)%20that%20will%20not%20grab%20the%20value.%20However%20there%20is%20a%20tricky%20way%20to%20resolve%20this%20issue%20by%20recording%20a%20simple%20Macro%20and%20trigger%20it%20before%20printing.%3C%2FP%3E%3CP%3EHere%20is%20a%20Tutorial%20that%20covers%20exactly%20that%20issue%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Ftime_continue%3D117%26amp%3Bv%3DSMdV7Pz6kg0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Ftime_continue%3D117%26amp%3Bv%3DSMdV7Pz6kg0%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20you%20do%20not%20want%20to%20watch%20the%20Tutorial%20then%20do%20the%20following%3A%3C%2FP%3E%3COL%3E%3CLI%3EHit%20ALT%20%2B%20F11%20%26gt%3B%26gt%3B%20opens%20the%20visual%20Basic%20editor%3C%2FLI%3E%3CLI%3ERight%20Clisk%20where%20it%20says%20%22This%20Workbook%22%3C%2FLI%3E%3CLI%3EAbove%20the%20white%20working%20area%20there%20are%202%20drop%20lists%3C%2FLI%3E%3CLI%3ESelect%20%22Workbook%22%20from%20the%20Left%20Drop%20List%3C%2FLI%3E%3CLI%3EDelete%20the%202%20lines%20that%20appeared%20automatically%3C%2FLI%3E%3CLI%3ECopy%20%26amp%3B%20Paste%20this%20%3CSTRONG%3Ecode%3A%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSTRONG%3E%3CEM%3EPrivate%20Sub%20Workbook_BeforePrint(Cancel%20As%20Boolean)%3C%2FEM%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3CEM%3EDim%20Sh%20As%20Worksheet%3C%2FEM%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3CEM%3EFor%20Each%20Sh%20In%20Me.Worksheets%3C%2FEM%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3CEM%3ESh.PageSetup.CenterHeader%20%3D%20Sh.Range(%22C5%22).Value%3C%2FEM%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3CEM%3ENext%20Sh%3C%2FEM%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3CEM%3EEnd%20Sub%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20replace%20CenterHeader%20by%20LeftHeader%20or%20RightHeader%3C%2FP%3E%3CP%3Ereplace%20C5%20by%20any%20cell%20of%20your%20choice%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20Other%20hand%20%2C%20in%20the%20Page%20Setup%20dialog%20box%20(Shortcut%20is%20ALT%2C%20P%2C%20S%2C%20P)%20%26gt%3B%26gt%3B%20Header%20Tab%20%26gt%3B%26gt%3B%20Custom%20Header%20%26gt%3B%26gt%3B%20You%20have%20some%20icons%20to%20include%20predefined%20functionality%20such%20as%20Date%2C%20Time%2C%20File%20Name%2C%20Sheet%20Nam%20...etc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%20you%3C%2FP%3E%3CP%3EIf%20you%20like%20my%20answer%20please%20mark%20it%20as%20an%20accepted%20one.%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-768042%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20the%20data%20from%20a%20cell%20into%20the%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-768042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20information%20on%20the%20macro%20option.%26nbsp%3B%20I%20look%20forward%20to%20reviewing%20and%20using%20this%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E
pda3476
New Contributor

I would like to use the content of cell A1, for example, as the recurring data in the worksheet's header.  Can #Excel do this?

2 Replies

@pda3476 

 

Hi,

You cannot reference a cell in the Header section (Say by typing =C5) that will not grab the value. However there is a tricky way to resolve this issue by recording a simple Macro and trigger it before printing.

Here is a Tutorial that covers exactly that issue:

https://www.youtube.com/watch?time_continue=117&v=SMdV7Pz6kg0

 

if you do not want to watch the Tutorial then do the following:

  1. Hit ALT + F11 >> opens the visual Basic editor
  2. Right Clisk where it says "This Workbook"
  3. Above the white working area there are 2 drop lists
  4. Select "Workbook" from the Left Drop List
  5. Delete the 2 lines that appeared automatically
  6. Copy & Paste this code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Sh As Worksheet
For Each Sh In Me.Worksheets
Sh.PageSetup.CenterHeader = Sh.Range("C5").Value
Next Sh
End Sub

 

You can replace CenterHeader by LeftHeader or RightHeader

replace C5 by any cell of your choice

 

 

On the Other hand , in the Page Setup dialog box (Shortcut is ALT, P, S, P) >> Header Tab >> Custom Header >> You have some icons to include predefined functionality such as Date, Time, File Name, Sheet Nam ...etc

 

Hope that helps you

If you like my answer please mark it as an accepted one.

Nabil Mourad

You'll learn how to link the page header to a specific cell with Excel VBA. The Excel macro is very simple. Best part is it runs automatically every time you print the page or save your Excel workbook or worksheet as PDF. ★★ Links to related videos: ★★ Excel Header Footer tutorial: ...
Highlighted

@nabilmourad

 

Thank you for the information on the macro option.  I look forward to reviewing and using this option.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies