Inserting a cell value into a header

Posted by Steve on November 10, 2000 8:42 AM

I want to set up a spreadsheet that will have a header that contains something like:

"Nominal Roll for " & Range("A5").value & " Station."
(New line)"For Week Ending " & Range("D5")

If I can set it using a VBA sub, so much the better.

Does anyone have an idea how to do this?

Thanks in advance,

Steve

Posted by Ben O. on November 10, 2000 12:03 PM

This should work:

Sub CustomHeader()
ActiveSheet.PageSetup.RightHeader = "Nominal Roll for " & Range("A5").Value & " Station." & Chr(10) & "For Week Ending " & Range("D5")
End Sub

Change Rightheader to Centerheader or Leftheader according to your preferences.

If you want to set the header for every worksheet in your workbook use this:

Sub CustomHeader()
For Each Sh In Sheets
Sh.Activate
ActiveSheet.PageSetup.RightHeader = "Nominal Roll for " & Sheets("Sheet1").Range("A5").Value & " Station." & Chr(10) & "For Week Ending " & Sheets("Sheet1").Range("D5")
Next Sh
End Sub

Just change "Sheet1" to the name of the sheet you want to pull the values from.

-Ben