Add sheet tab name to header 4

[mscallisto]

Can I add the sheet-tab-name to LeftHeader, If so how?

As I print each sheet I would like that sheet name to appear in my header.

Sub Set_Head_Foot()
  With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "&""Comic Sans MS,Italic""&10" & ActiveWorkbook.Path + "\" + ActiveWorkbook.Name
    .RightHeader = "&D"
    .LeftFooter = ""
    .CenterFooter = "&""Comic Sans MS,Italic""&10" & ActiveWorkbook.FullName
    .RightFooter = ""
  End With
End Sub

 

[SkipVought]

Did you try macro recording it?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mscallisto]

No Skip I didn't; there is no place (that I can see) in Header/Footer to add the Sheet tab name.

 

[macropod]

Hi mscallisto,

Try:

Sub UpdateLeftHeader()
ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Name
End Sub

Cheers

[MS MVP - Word]

 

[mscallisto]

Thanks macropod thats exactly what I wanted ((~_~)))

 

[firefytr]

Why not use the workbook before print event then? No real sense in creating a separate sub routine to apply to each sheet individually (if that is what you are doing), just manipulate the event in the ThisWorkbook module...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    On Error Resume Next
    ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Name
    If Err = 0 Then Exit Sub
    ActiveChart.PageSetup.LeftHeader = ActiveChart.Name
End Sub

(Should work with chart sheets as well.)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[combo]

All of the above data can be build with formatting codes. Moreover, you can combine regular text and codes (for instance .LeftHeader="Sheet name: &A").

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.

combo

 

[SkipVought]

"No Skip I didn't; there is no place (that I can see) in Header/Footer to add the Sheet tab name."

REALLY???? You can borrow my glasses.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mscallisto]

OK Skip e-mail me those $^%#@# glasses!!

I created the original code with macro record and the only options I saw are:

&[Page]&[Pages]&[Date]&[Time]&[Path]&[File]&[Tab]&[Picture]

where am I missing ActiveSheet.Name?
and where is Mike Lowell going??

I put my code in:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART\personal.xls
thinking that it would print headings for each and every .xls file I printed.

It does but only if I run the macro at least once and in each sheet. I reckon I need to investigate firefytr's suggestions but I dread VBA Help. I suppose it's just fine for those who are proficient but it's Dante's Inferno for me.

Anyway thanks to all for the fine assistance.

sam

 

[SkipVought]

Sub Macro1()
'
' Macro1 Macro[b]
' Macro recorded 11/15/2007 by SkipVought[/b]
'

'
    With ActiveSheet.PageSetup[b]
        .LeftHeader = "&A"[/b] 'tab name in left header
'.........

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mscallisto]

I'm guessing that you entered &A in the left header???

 

[mscallisto]

And Thanks Combo but I had to rearrange your list so I could remember easier:

Format code description

&F Prints the name of the document.
&I Turns italic printing on or off.
&X Turns superscript printing on or off.
&A Prints the name of the workbook tab.
&B Turns bold printing on or off.
&L Left aligns the characters that follow.
&E Turns double-underline printing on or off.

&C Centers the characters that follow.
&R Right aligns the characters that follow.
&Y Turns subscript printing on or off.
&P Prints the page number.
&T Prints the current time.

&S Turns strikethrough printing on or off.
&P +number Prints the page number plus the specified number.
&U Turns underline printing on or off.
&D Prints the current date.

 

[SkipVought]

No. I did not enter &A.

I hit the TAB icon in the Page Setup - HEADER dialog whil recording the macro.

Its ALL RECORDED.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mscallisto]

OK so where's those glasses; I definitely need them!!!!

I would have sworn I tried Tab but evidently NOT.

Thanks Skip (I never doubted you)

sam