Page numbering

[Lp12]

Hi,
I want to have the "page n of nn pages" to be represented in a specific cell
(not using the header functions). Is it possible to do so?
Thanks a lot in advance

 

[JLGWhiz]

It is possible. This snippet will get you started.

Sub dj()
Sheets(1).DisplayPageBreaks = True
pbRng = Sheets(1).HPageBreaks(1).Location.Address
Range(pbRng).Offset(-50, 0) = "Pg 1 of 1"
End Sub

If the worksheet is using rows with standard height, then the snippet above
would put the page number in cell A2. What the code has to do to number all
pages is as follows:

1. count all HPageBreaks. (Top of page 1 does not count)
2. set up a loop that will:
a. Use variable to find the next HPageBreak
b. Use variables change the page of pages number

Note that the first line in the snippet displays the page breaks. They must
be visible for the count and location properties to work.

 

[JLGWhiz]

Not to discourage your efforts in creating a procedure to do what you want
with the page numbering, but there are a few things to bear in mind as you
do so. Excel is subject to users adding rows and columns as well as varying
their height and width respectively. Cells might be shifted as users
configure data on the worksheets and a few other quirks that can alter page
numbers, even if you can get them calculated correctly the first time.
Trying to write code to cover all of these possibilities could be a
nightmare. I think that is why it is not a built in feature and why the
internal calculation that is done is kept until the print preview is called.
At that point, it can calculate what would be printed based on the page
setup parameters that the user has entered. That is another glitch, the
user can also change between protrait and landscape, or change the paper
size. It goes on and on. While it might be convenient to see which
printed page you are working on as you go through a worksheet, I am not sure
it is a real advantage in terms of the effort it would take to write the
code to get it.

 

[Rick Rothstein]

Another complication is the numbering can be selected by the user to be
numbered either "down then over" or "over then down", so your code would
have to take that sequential ordering method into account as well. On top of
that, if I remember correctly, when there are non-contiguous areas selected
(which may not be applicable to the OP's question), I believe the order the
areas were selected in figures into how the page numbers are assigned. I
looked at trying to develop a generalized routine to determine the page
number for a given range and just met so many condition that needed to be
accounted for that I wasn't able to complete the task; however, I did sit
back after that experience and marveled over the fact that Excel can do all
of what is required in the "blink of an eye"... I can just imagine what a
"mess" the underlying Excel code has to be.

 

[Project Mangler]

This is a case of where angels are too smart to tread:

It only numbers down then over and puts n of nn pages into the top right
cell in the page.

I leave modifications as an exercise for the reader.

Sub PageNr()
Dim pNrs As String
Dim cnt As Long
Dim n As Long
Dim p As Long
Dim pgHLen As Long
Dim pgVLen As Long
Dim riteCol As Long
Dim btmRow As Long
Dim hBound As Long
Dim vBound As Long

btmRow = Cells(Rows.Count, 1).End(xlUp).Row
riteCol = Cells(1, Columns.Count).End(xlToLeft).Column
pgHLen = ActiveSheet.HPageBreaks.Item(1).Location.Row - 1
pgVLen = ActiveSheet.VPageBreaks.Item(1).Location.Column - 1

If btmRow Mod pgHLen > 0 Then
hBound = 1
Else
hBound = 0
End If
If riteCol Mod pgVLen > 0 Then
vBound = 1
Else
vBound = 0
End If

pNrs = (ActiveSheet.HPageBreaks.Count + hBound) *
(ActiveSheet.VPageBreaks.Count + vBound)

cnt = 1
For p = 1 To riteCol Step pgVLen
For n = 1 To btmRow Step pgHLen
ActiveSheet.Cells(n, p) = cnt & " of " & pNrs & " pages"
cnt = cnt + 1
Next n
Next p

End Sub

 

[Project Mangler]

This is a case of where angels are too smart to tread:

It only numbers down then over and puts n of nn pages into the top left
cell in the "page".

I leave modifications as an exercise for the reader.

Sub PageNr()
Dim pNrs As String
Dim cnt As Long
Dim n As Long
Dim p As Long
Dim pgHLen As Long
Dim pgVLen As Long
Dim riteCol As Long
Dim btmRow As Long
Dim hBound As Long
Dim vBound As Long

btmRow = Cells(Rows.Count, 1).End(xlUp).Row
riteCol = Cells(1, Columns.Count).End(xlToLeft).Column
pgHLen = ActiveSheet.HPageBreaks.Item(1).Location.Row - 1
pgVLen = ActiveSheet.VPageBreaks.Item(1).Location.Column - 1

If btmRow Mod pgHLen > 0 Then
hBound = 1
Else
hBound = 0
End If
If riteCol Mod pgVLen > 0 Then
vBound = 1
Else
vBound = 0
End If

pNrs = (ActiveSheet.HPageBreaks.Count + hBound) *
(ActiveSheet.VPageBreaks.Count + vBound)

cnt = 1
For p = 1 To riteCol Step pgVLen
For n = 1 To btmRow Step pgHLen
ActiveSheet.Cells(n, p) = cnt & " of " & pNrs & " pages"
cnt = cnt + 1
Next n
Next p

End Sub