How to include "Defined Names" in Headers and Foot

[jroo]

Hi,

I have a worksheet where I want users to enter their information on one tab. I want to Define (i.e. Insert -> Name -> Define...) names which will be called:

Name
Company
Telephone

Is there a way to insert these defined names in the header? ...In pagesetup Header/Footer

Also, in the past I've seen that you can include a directory path in the header and footer...however, I think this option may have somehow been lost in my settings. Am I making sense?

I see icon buttons for: Date, Time, Tab, Filename, Page, and Pages.

But no others. How do I include directory path (i.e. C:\My Documents)

 

[anvil19]

Re: How to include "Defined Names" in Headers and

Hey jroo

The formatting codes (from Excel help) for headres and footers are:

Format code Description
&L Left aligns the characters that follow.
&C Centers the characters that follow.
&R Right aligns the characters that follow.
&E Turns double-underline printing on or off.
&X Turns superscript printing on or off.
&Y Turns subscript printing on or off.
&B Turns bold printing on or off.
&I Turns italic printing on or off.
&U Turns underline printing on or off.
&S Turns strikethrough printing on or off.
&D Prints the current date.
&T Prints the current time.
&F Prints the name of the document.
&A Prints the name of the workbook tab.
&P Prints the page number.
&P+number Prints the page number plus the specified number.
&P-number Prints the page number minus the specified number.
&& Prints a single ampersand.
& "fontname" Prints the characters that follow in the specified font. Be sure to include the double quotation marks.
&nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
&N Prints the total number of pages in the document.

As for adding names into the header, I saw something about that just recently, but for the life of me I can't remember where. I'll keep looking for it and post back if I find it.

anvil19

 

[Ivan F Moala]

You could do something like this;

Amend code to suit...as it is it prints a footer on the last page

Sub Prt_FooterOnLastPage()
Dim LastPage
Dim Lft As String, Ctr As String, Rght As String

Lft = ActiveSheet.Range("Name")       '// Left Text to print
Ctr = ActiveSheet.Range("Company")    '// Centre text to print
Rght = ActiveSheet.Range("Telephone") '// Right Txt to Prt
LastPage = ExecuteExcel4Macro("GET.DOCUMENT(50)")

If LastPage = 1 Then GoTo OnePage

With ActiveSheet.PageSetup
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
End With

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=LastPage - 1

OnePage:
With ActiveSheet.PageSetup
    'ActiveSheet.Range("A1") 'change A1 to your cell input
    .LeftFooter = "&l" & "&""Arial Black,Bold""&12" & Lft
    'ActiveSheet.Range("B1") 'change B1 to your cell input
    .CenterFooter = "&""Arial Black,Bold""&8 " & Ctr
    ' ActiveSheet.Range("C1") 'change C1 to your cell input
    .RightFooter = "&""Times New Roman,Bold""&16 " & Rght
End With

'ActiveWindow.SelectedSheets.PrintOut From:=LastPage, To:=LastPage

ActiveWindow.SelectedSheets.PrintPreview

End Sub