How do I include a cell-value in Excel header?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
 
Back
Top