Page Numbering in Excel

emady

Board Regular
Joined
Feb 19, 2003
Messages
228
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

Board Regular
Joined
Feb 19, 2003
Messages
228
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,885
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:
Code:
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:
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,053
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top