Page Numbering in Excel

[emady]

Hi,

I have a formatted sheet that is several pages long and has several repeating rows at the top that form the header. What I am trying to do is have the page numbers displayed in this header but excel doesn't have built in functions to do so.
I managed so far to get a count of the total number of pages to be printed using VBA but am not sure how to detect individual pages and update the page cell on the sheet.

I would appreciate any help I can get.

Elia

 

[onlyadrafter]

Hello,

In the PAGE SET UP --> HEADER click the icons showing a sheet,l one is for &[Page] and the other is &[Pages] enter 'of' in between, and the page numbers should be ahown. You can adjust the first page number in the PAGE tab. (this is on V97).

Is this any help, or am I missing the point all together?

 

[emady]

No I am not using the page setup header, the header is the first six rows on the sheet itself and they repeat at the top of each page when printing.

 

[sharper31]

Did you find a solution to this? Would love to hear it if you have.

 

[pgc01]

Hi emady

One solution is to replace the excel automatic printing with your own. You can loop print one page at a time and modify whatever you want.

For example:

First you tell excel that in the case of worksheet "Sheet1" you take care of the printing yourself.

Paste in the workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If ActiveSheet.Name = "Sheet1" Then
    Application.EnableEvents = False
    Call PrintPages
    Application.EnableEvents = True
    Cancel = True
End If
End Sub

Then in a general module you write your own printing macro.
For example, in this macro I write the page number in A1:

Sub PrintPages()
Dim lPage As Long

With ActiveSheet
    For lPage = 1 To (1 + .HPageBreaks.Count) * (1 + .VPageBreaks.Count)
        Range("A1") = lPage
        .PrintOut from:=lPage, To:=lPage
    Next lPage
End With
End Sub

Does this solve your problem?
PGC