PDA

View Full Version : Cell Value in Header



Anne Troy
08-26-2005, 09:44 AM
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
08-26-2005, 09:58 AM
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
08-26-2005, 09:58 AM
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
08-26-2005, 10:24 AM
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



:hi:

Ken Puls
08-26-2005, 10:27 AM
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! :rotlaugh:

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

lucas
08-26-2005, 10:34 AM
That makes sense, Thanks Ken..:yes

MWE
08-26-2005, 12:10 PM
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
08-26-2005, 12:26 PM
Well then...

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