Cell Value in Header

[Anne Troy]

Stuck my foot in it and now feel obligated to answer a newsgroup question.

So... help me out, guys. How is this accomplished?

Let's assume I want A1's value to appear in my header.

[lucas]

Hi Anne,

This is a workbookbeforePrint event that adds the cell value from one sheet into each worksheets header.

Option Explicit 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim WS As Worksheet
For Each WS In Worksheets
WS.PageSetup.LeftHeader = Worksheets("Sheet1").Range("A3").Value
Next WS
End Sub

[Ken Puls]

Hi Anne,

I have no idea if this can be done without code, so maybe I'll learn something here...

VBA ways:

Sub AddHeader()
'Add A1 from active sheet to active sheet's header
With ActiveSheet.PageSetup
    .LeftHeader = Range("A1").Value
End With
End Sub

Sub AddHeaderToAll_1()
'Add A1 from each sheet to header
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.PageSetup.LeftHeader = ws.Range("A1").Value
Next ws
End Sub

Sub AddHeaderToAll_2()
'Add A1 from active sheet to each sheets's header
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.PageSetup.LeftHeader = ActiveSheet.Range("A1").Value
Next ws
End Sub

HTH,

[lucas]

Any reason this wouldn't work Ken?

Option Explicit
'Add A1 from active sheet to each sheets's header
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Range("A1").Value
End Sub

[Ken Puls]

Not at all. It just runs every time you print, mine runs once. If the value of the range is always changing and needs updating frequently, then yours would be the right way to go. If its pretty stagnant, then mine saves that extra milisecond that no one would notice anyway!

Just different ways to skin a cat, really...

[lucas]

That makes sense, Thanks Ken..

[MWE]

Hi Anne,

I have no idea if this can be done without code, so maybe I'll learn something here...

I have poked at this several times and concluded each time that the only reliable method was to use code.

There are lots of things re the management of headers and footers that could be improved

[Ken Puls]

Well then...

I guess some variants of these should make it into the KB then.