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

  • Thread starter Thread starter tmacke
  • Start date Start date
T

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?
 
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
 
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.
 
Back
Top