how to include a variable in the header and/or footer of a worksheet

[tmacke]

Hi, I am building a template to be used by multiple teams. I would like them to enter their team name once in the first worksheet of a multi sheet workbook. I would like to have the team name appear in the header and footer of each worksheet also without having to modify the header or footer manually. Is there a way to do this?

[Casey]

Hi,
In the example below the Team puts there name in a cell that is a named range called TeamName.
INSERT > NAME >DEFINE
This named range is on a worksheet named Entry Sheet.
Change both of these to suit.

Place this code in the ThisWorkbook module.
Right click a worksheet tab choose view code.
Double click ThisWorkbook

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call AddNameToHeadFoot
End Sub
Private Sub AddNameToHeadFoot()
Dim str As String
str = Sheets("Entry Sheet").Range("TeamName").Value 'Modify sheet name and named range to suit
ActiveSheet.PageSetup.RightFooter = "&""""&10" & str '<==== 10 is font size
ActiveSheet.PageSetup.RightHeader = "&""""&10" & str '<==== 10 is font size
End Sub

You can use these variables to place the team names in different areas of the headers and footers
.LeftHeader
.CenterHeader
.RightHeader
.LeftFooter
.CenterFooter
.RightFooter


HTH

[Ron2005]

if by header you simply mean the top of the other tabs then in the sell
you want it to appear on enter:

='sheetnameoffirstworksheet!(A10) change A10 to the cell
number where it is on the first worksheet.