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

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?


--
tmacke
------------------------------------------------------------------------
tmacke's Profile: http://www.excelforum.com/member.php...o&userid=16817
View this thread: http://www.excelforum.com/showthread...hreadid=532985

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

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


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=532985

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

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.