Setting sheet names as headers with page numbering

[KimberlyC]

Hi
Is there a way (before I print the activeworkbook) to set the header of each
worksheet in my workbook to the sheet's name plus the [page] number?

There can be many sheets in the activeworkbook with different names.... and
with many pages that print on each sheet (that's why I need the page number
at the end of each sheet's name in the header).

Any help on this would be greatly appreciated!!
Thanks in advance
Kimberly

 

[Jef Gorbach]

Hi
Is there a way (before I print the activeworkbook) to set the header of each
worksheet in my workbook to the sheet's name plus the [page] number?

There can be many sheets in the activeworkbook with different names.... and
with many pages that print on each sheet (that's why I need the page number
at the end of each sheet's name in the header).

Any help on this would be greatly appreciated!!
Thanks in advance
Kimberly

try this:

Sub title_pages()
'change month in center heading before running
For i = 1 To Sheets.Count
With Sheets(i).PageSetup
.LeftHeader = ""
.CenterHeader = _
"Company Name" & Chr(10) _
& "&A" & Chr(10) _
& Format(DateSerial(Year(Now), Month(Now) - 1, 1), "mmm-yyyy")
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
End With
Next i
End Sub

 

[Gord Dibben]

Kimberly

Sub Name_in_Header()
For i = 1 To Sheets.Count
With Sheets(i).PageSetup
.CenterHeader = Sheets(i).Name & " Page &P"
End With
Next i
End Sub


Gord Dibben Excel MVP

 

[KimberlyC]

Thank you!!
They both work well!!