Would that be the value of B4 on each sheet or B4 from one sheet replicated to
all sheets?

1. Whichever the case, you would require a macro to do this.

2. You can create a sheet template that has a header of your choosing, but
would be hard-coded.

Easiest is to just insert the sheet then run the macro to add the selectable
header to that new sheet.

Sub Sheet1_Cell_In_AllHeaders()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Sheets
WS.PageSetup.LeftHeader = Sheets(1).Range("B4").Value
Next
End Sub


Sub Sheet_Cell_In_SheetlHeader()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Sheets
WS.PageSetup.LeftHeader = WS.Range("B4").Value
Next
End Sub


Gord Dibben MS Excel MVP


On Sat, 17 Feb 2007 11:51:05 -0800, Hershmab
wrote:

I have a workbook with 12-30 worksheets that are in the same format. I want
to create identical headers for all of them, distinguished only by including
the value of, say, cell B4 as part of the header text.
I have two questions:
(1) How do I get a variable cell-value into any header?
(2) I know how to give any new worksheet the same header as an existing
worksheet, but is there any way of creating a default header that will
automatically attach itself to newly-created worksheets?