Show sheet name in print footer

[mikeymay]

I have recorded a macro to get the code to add the active sheet name to the centre footer of a print out but this doesn't seem to work

.CenterHeader = "&""Arial,Bold""&36&A"

I can't see anything that tells it to use the sheet name.

Any ideas anyone?


Thanks

Seem to be having a problem with the whole code as well

Sub PrintGraph()

Dim objChart As Object

For Each objChart In ActiveSheet.ChartObjects
    With objChart.Chart
        .PageSetup.Orientation = xlLandscape
        .CenterHeader = "&""Arial,Bold""&36&A"
        .LeftMargin = Application.InchesToPoints(0.393700787401575)
        .RightMargin = Application.InchesToPoints(0.393700787401575)
        .TopMargin = Application.InchesToPoints(0.590551181102362)
        .BottomMargin = Application.InchesToPoints(0.590551181102362)
        .HeaderMargin = Application.InchesToPoints(0.511811023622047)
        .FooterMargin = Application.InchesToPoints(0.511811023622047)
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintPreview
    End With
    objChart.Chart.PrintPreview
'    objChart.Chart.PrintOut
Next objChart

End Sub

Thanks

 

[Trevor G]

Try this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> sheetname()<br><SPAN style="color:#007F00">'Add sheet name in footer</SPAN><br>ActiveSheet.PageSetup.LeftFooter = ActiveSheet.Name<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mikeymay]

Will this add the sheet name to the centre footer of the chart I am printing?

 

[Trevor G]

It will add this to the Worksheet, I am assuming you have the chart on a separate worksheet rather than embedded into a sheet?

IF you want it in the footer then adjust the word left to center

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> sheetname()<br><SPAN style="color:#007F00">'Add sheet name in sheet footer</SPAN><br>ActiveSheet.PageSetup.CenterFooter = ActiveSheet.Name<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mikeymay]

The chart is embedded on the worksheet rather than a seperate chart worksheet.

 

[sanjayplayer]

Go to print preview, go to setup, header & footer, custom footer, choose sheet name from options[Excel logo], it will show in the every prints
===easy as that
Sanjay

 

[mikeymay]

Afraid it's not quitre as easy as that...

I have a data table and an embedded graph on the worksheet named 'Area 1' and there are multiple worksheets - Area 3, Area 3, etc.

I have placed 2 buttons on the worksheet to print either the data table or the graph and the code then sets the pagesetup accordingly.

 

[mikeymay]

Forgot to mention that the data table has a different footer to the graph, so needs to be changed dependant on what is being printed.


Thanks