SOLVED

Auto Populate Header Using Another Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2369339%22%20slang%3D%22en-US%22%3EAuto%20Populate%20Header%20Using%20Another%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369339%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20years%20I%20have%20been%20struggling%20with%20a%20document%20that%20I%20change%20several%20times%20a%20month%2C%20and%20know%20there%20is%20an%20easier%20way%20to%20solve%20this%20issue.%20It%20seems%20like%20auto%20populating%20a%20header%20should%20be%20easy%20to%20do%2C%20right%3F%3C%2FP%3E%3CP%3EI%20have%20an%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Fpostpage%2Fboard-id%2FExcelGeneral%2Fattachments-key%2F53ecf295-cd1a-4961-b5f8-cc6945101f75%2Fload-autosave%2Ftrue%23%22%20target%3D%22_blank%22%3EExcel%26nbsp%3B%3C%2FA%3Edocument%20(attached)%20with%20two%20Sheets%2C%20named%20A%20and%20B.%20I%20would%20like%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Fpostpage%2Fboard-id%2FExcelGeneral%2Fattachments-key%2F53ecf295-cd1a-4961-b5f8-cc6945101f75%2Fload-autosave%2Ftrue%23%22%20target%3D%22_blank%22%3EFormulas%20and%20Functions%3C%2FA%3E%20to%20auto%20populate%20the%20header%20on%20Sheet%20B%20from%20cells%20on%20Sheet%20A.%20There%20will%20only%20ever%20be%20alphanumeric%20used%3B%20sometimes%20it%20is%20a%20few%20words%20and%20other%20times%20it%20could%20be%20fifteen%20words.%3C%2FP%3E%3CP%3EOn%20Sheet%20B%2C%20the%20'Job%20Name'%20(seen%20as%20Room%20Renovation)%20needs%20to%20auto%20populate%20from%20Cell%20C6%20on%20Sheet%20A%20and%20be%20centered%2C%20Bold%2C%20Ariel%2C%2014%20point%20font%2C%20without%20other%20text.%20The%20font%20will%20remain%20the%20same%20for%20the%20entire%20header.%20The%20next%20line%20starts%20with%20%22PO%20%23%22.%20There%20will%20not%20be%20a%20space%20followed%20by%20the%20'Job%20Number'%20(seen%20as%204302).%20The%20header%20seems%20to%20be%20divided%20into%20thirds%2C%20so%20I%20prefer%20this%20text%20to%20be%20centered%20in%20the%20middle%20third%20header%20box.%3C%2FP%3E%3CP%3EThis%20is%20a%20very%20simplified%20version%20of%20my%20issue.%20I%20have%20a%20complex%20file%20that%20has%20multiple%20Sheets%20with%20links%20and%20formulas%2C%20so%20the%20simplest%20solution%20is%20my%20preference%20please.%20Thank%20you%20to%20everyone%20who%20took%20the%20time%20to%20look%20at%20this%20for%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2369339%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369543%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Populate%20Header%20Using%20Another%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1058121%22%20target%3D%22_blank%22%3E%40mschonefeld%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20requires%20VBA.%20For%20example%20in%20the%20Worksheet_Activate%20event%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ERight-click%20the%20sheet%20tab%20of%20sheet%20B.%3C%2FLI%3E%0A%3CLI%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FLI%3E%0A%3CLI%3ECopy%20the%20code%20listed%20below%20into%20the%20worksheet%20module.%3C%2FLI%3E%0A%3CLI%3ESwitch%20back%20to%20Excel%20and%20save%20the%20workbook.%3C%2FLI%3E%0A%3CLI%3EThe%20header%20will%20be%20updated%20when%20you%20activate%20sheet%20B.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Activate()%0A%20%20%20%20Me.PageSetup.CenterHeader%20%3D%20_%0A%20%20%20%20%20%20%20%20%22%26amp%3B%22%22Arial%22%22%26amp%3B14%26amp%3BB%22%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20Worksheets(%22A%22).Range(%22H6%22).Value%20%26amp%3B%20vbLf%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22PO%20%23%22%20%26amp%3B%20Worksheets(%22A%22).Range(%22C6%22).Value%20%26amp%3B%20vbLf%20%26amp%3B%20vbLf%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22PRELIMINARY%22%20%26amp%3B%20vbLf%20%26amp%3B%20vbLf%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22COLOR%20SAMPLE%20REQUEST%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2373863%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Populate%20Header%20Using%20Another%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BI%20want%20to%20thank%20you%20for%20saving%20me%20years%20of%20frustration.%3C%2FP%3E%3CP%3EI%20did%20have%20to%20enable%20macros%2C%20but%20nothing%20difficult.%20Thank%20you%20again%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2374053%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Populate%20Header%20Using%20Another%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374053%22%20slang%3D%22en-US%22%3EI%20would%20like%20to%20be%20able%20to%20add%20the%20Date%20and%20Time%20printed%20in%20the%20RightFooter%2C%20but%20keep%20getting%20script%20errors%20when%20I%20alter%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F21903%22%20target%3D%22_blank%22%3E%40hans%3C%2FA%3E_Vogelaar's%20script%20to%20include%20this.%20Also%20I%20would%20like%20to%20include%20a%20line%20just%20above%20the%20print%20date%2Ftime%20that%20includes%20%22CC%22Field%2C%20Job%23%20%22%20and%20then%20pulls%20data%20from%20H6.%3CBR%20%2F%3EAny%20help%20would%20be%20greatly%20appreciated.%3CBR%20%2F%3EThanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2374966%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Populate%20Header%20Using%20Another%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1058121%22%20target%3D%22_blank%22%3E%40mschonefeld%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20slightly%20confused%20by%20that%2C%20but%20see%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

For years I have been struggling with a document that I change several times a month, and know there is an easier way to solve this issue. It seems like auto populating a header should be easy to do, right?

I have an Excel document (attached) with two Sheets, named A and B. I would like Formulas and Functions to auto populate the header on Sheet B from cells on Sheet A. There will only ever be alphanumeric used; sometimes it is a few words and other times it could be fifteen words.

On Sheet B, the 'Job Name' (seen as Room Renovation) needs to auto populate from Cell C6 on Sheet A and be centered, Bold, Ariel, 14 point font, without other text. The font will remain the same for the entire header. The next line starts with "PO #". There will not be a space followed by the 'Job Number' (seen as 4302). The header seems to be divided into thirds, so I prefer this text to be centered in the middle third header box.

This is a very simplified version of my issue. I have a complex file that has multiple Sheets with links and formulas, so the simplest solution is my preference please. Thank you to everyone who took the time to look at this for me.

5 Replies
best response confirmed by mschonefeld (Occasional Contributor)
Solution

@mschonefeld 

This requires VBA. For example in the Worksheet_Activate event:

  • Right-click the sheet tab of sheet B.
  • Select 'View Code' from the context menu.
  • Copy the code listed below into the worksheet module.
  • Switch back to Excel and save the workbook.
  • The header will be updated when you activate sheet B.
Private Sub Worksheet_Activate()
    Me.PageSetup.CenterHeader = _
        "&""Arial""&14&B" & _
        Worksheets("A").Range("H6").Value & vbLf & _
        "PO #" & Worksheets("A").Range("C6").Value & vbLf & vbLf & _
        "PRELIMINARY" & vbLf & vbLf & _
        "COLOR SAMPLE REQUEST"
End Sub

@Hans Vogelaar I want to thank you for saving me years of frustration.

I did have to enable macros, but nothing difficult. Thank you again @Hans Vogelaar 

I would like to be able to add the Date and Time printed in the RightFooter, but keep getting script errors when I alter @hans_Vogelaar's script to include this. Also I would like to include a line just above the print date/time that includes "CC"Field, Job# " and then pulls data from H6.
Any help would be greatly appreciated.
Thanks again.

@mschonefeld 

I'm slightly confused by that, but see the attached version.

Thank you again @hans_Vogelaar