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?

For the header, just use concatenation.
="Title you want "&B4
or
="Title "&B4&" rest of title"
or
=B4&" Title you want"

You could create a template sheet that has your headers and no data, then
whenever you need to make a new sheet, you just copy the template sheet.

- KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"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?

Try the code
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterHeader = .Range("b4").Text
End With
End Sub
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"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?

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?