SOLVED
Home

Pageheader and footer

%3CLINGO-SUB%20id%3D%22lingo-sub-785425%22%20slang%3D%22en-US%22%3EPageheader%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785425%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20creating%20a%20multisheet%20workbook%20where%20I%20want%20to%20insert%20the%20contens%20of%20a%20cell%20into%20the%20pagefoot%2C%20is%20that%20possible%20and%20in%20that%20case%20how.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-785425%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785580%22%20slang%3D%22en-US%22%3ERe%3A%20Pageheader%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386278%22%20target%3D%22_blank%22%3E%40johpeswed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EAlthough%20you%20can%20add%20different%20types%20of%20information%20to%20the%20Header%20or%20Footer%20(File%20Name%2C%20Sheet%20Name%2C%20Date%2C%20...etc)%2C%20however%2C%20using%20the%20Page%20Setup%20dialog%20box%2C%20you%20cannot%20grab%20a%20value%20from%20a%20cell%20in%20the%20worksheet%20by%20typing%20%3DcellReference%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EThe%20Good%20News%3C%2FSTRONG%3E%3C%2FFONT%3Eis%2C%20You%20can%20create%20a%20very%20simple%20code%20in%20VBA%20to%20do%20that%20for%20you.%3C%2FP%3E%3CP%3EThe%20code%20should%20be%20attached%20to%20the%20Before%20Print%20Event%20of%20the%20Workbook%3A%3C%2FP%3E%3CUL%3E%3CLI%3EGo%20To%20the%20Visual%20Basic%20Editor%20%26gt%3B%26gt%3BALT%20%2B%20F11%3C%2FLI%3E%3CLI%3ETo%20the%20Left%20side%20(Project%20Explorer)%20%26gt%3B%26gt%3B%20Right%20Click%20ThisWorkbook%20%26gt%3B%26gt%3B%26nbsp%3B%3C%2FLI%3E%3CLI%3ESelect%20View%20Code%3C%2FLI%3E%3CLI%3EIn%20the%20VBE%26gt%3B%26gt%3B%20click%20on%20the%20drop%20list%20to%20the%20left%20and%20select%20WorkBook%3C%2FLI%3E%3CLI%3EThen%20click%20on%20the%20drop%20list%20to%20the%20right%20and%20select%20Before%20Print%3C%2FLI%3E%3CLI%3EAssuming%20you%20want%20to%20link%20to%20the%20value%20in%20Cell%20C5%20in%20each%20sheet%2C%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EHere%20is%20the%20code%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3E%3CSTRONG%3EPrivate%20Sub%20Workbook_BeforePrint(Cancel%20As%20Boolean)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20Sh%20As%20Worksheet%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20Each%20Sh%20In%20Me.Worksheets%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Sh.PageSetup.CenterFooter%20%3D%20Sh.Range(%22C5%22).Value%20%26amp%3B%20VBTab%20%26amp%3B%20Date%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%20Sh%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3E%3CSTRONG%3EEnd%20Sub%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENB%3A%20To%20have%20in%20the%20Header%20use%20CenterHeader%20instead%3C%2FP%3E%3CP%3EVBTab%20will%20add%20a%20Space%20and%20I%20am%20joining%20to%20the%20Date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786662%22%20slang%3D%22en-US%22%3ERe%3A%20Pageheader%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786662%22%20slang%3D%22en-US%22%3EThank%20you%20Nabil%3CBR%20%2F%3EThis%20was%20just%20what%20I%20wanted%3CBR%20%2F%3E%3CBR%20%2F%3Ejohpeswed%3C%2FLINGO-BODY%3E
johpeswed
New Contributor

I am creating a multisheet workbook where I want to insert the contens of a cell into the pagefoot, is that possible and in that case how.

2 Replies
Solution

@johpeswed 

Hi

Although you can add different types of information to the Header or Footer (File Name, Sheet Name, Date, ...etc), however, using the Page Setup dialog box, you cannot grab a value from a cell in the worksheet by typing =cellReference

 

The Good News is, You can create a very simple code in VBA to do that for you.

The code should be attached to the Before Print Event of the Workbook:

  • Go To the Visual Basic Editor >>ALT + F11
  • To the Left side (Project Explorer) >> Right Click ThisWorkbook >> 
  • Select View Code
  • In the VBE>> click on the drop list to the left and select WorkBook
  • Then click on the drop list to the right and select Before Print
  • Assuming you want to link to the value in Cell C5 in each sheet,

Here is the code:

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim Sh As Worksheet

    For Each Sh In Me.Worksheets

    Sh.PageSetup.CenterFooter = Sh.Range("C5").Value & VBTab & Date

    Next Sh

End Sub

 

NB: To have in the Header use CenterHeader instead

VBTab will add a Space and I am joining to the Date

 

 

Hope that Helps

Nabil Mourad

Thank you Nabil
This was just what I wanted

johpeswed
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies