Dynamic pageheader

[helpme]

is any one know how do I set pageheader dynemically?
So when I see the printpreview Header will change AS PAGE
Change

Thanks

 

[Frank Kabel]

Hi
as an example: The following will put the value from cell A1 into the
header. Change A1 and the header will change accordingly:

Put the following macro in your workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterHeader = wkSht.range("A1").value
End With
Next wkSht
End Sub

 

[hemlme]

Thank for reply.
I still have one for questions
Could you please tell me how do I get the total no of
pages in advaced whenever the manual page break changed
and print that no in header as page 1 of 13?

 

[Frank Kabel]

Hi
Excel supports this directly in the pagesetup dialog.

 

[helpme]

but I would like to do in code because I have report which
has more than 100 pages so when ever costcenter changed in
report, I would like to know in advanced how many pages
that cost center has and print that costcenter plus start
page numbering with like page 1 of 13 as a header for that
cost center and so on

Thanks a lot

 

[Frank Kabel]

o.k.
try the following:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterHeader = wkSht.range("A1").value
.leftHeader = "&P of &N"
End With
Next wkSht
End Sub