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.