Excel: Cell Value in Header 1

[guava65]

I am trying to automate a user workbook that is used as a template. The problem they are having is that the users are inept at setting header/footers for each worksheet.

I would like to set up code that will pull text or a number from a worksheet cell(as a named range preferrably) and use that in the header.]

I know how to set-up a headers with fixed text but cannot figure out how to use a variable from a worksheet. Any ideas?



Aloha,
cg

 

[PHV]

I know how to set-up a headers with fixed text but cannot figure out how to use a variable from a worksheet
Just replace the fixed text with a Cell value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Loomah]

Just to expand on what PHV has said, something like this should do it using named ranges

With Worksheets("Sheet1").PageSetup
    .CenterHeader = Range("Head")
    .LeftFooter = Range("Foot")
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[guava65]

Thank you Loomah for the help. I really need to more practice in VBA. That's for sure!

One more question (if you don't mind...):

How do I encorporate font size and attribute: (&14 &B)?

It seems no matter where I put the variable I get an error.

Thanks in advance.

Aloha,
cg

 

[guava65]

I figured it out. It just took a bit more expirimenting.

For others trying to figure it out this is what I did.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Dim sht As Worksheet
  For Each sht In ThisWorkbook.Sheets
  sht.PageSetup.LeftHeader = "&14 &B [b][I]FIXED TEXT[/I][/b]"
  sht.PageSetup.RightHeader = "&14 &B [b][I]FIXED TEXT[/I][/b]" & Range("[b][I]RANGE NAME[/I][/b]")
  Next sht
End Sub

Thanks for the help!


Aloha,
cg