page numbering in an Excel cell

[mark]

I need to be able to number a multi-page excel document with the 'page 1
of 2' etc appearing in a particular cell.

I canonly do this in the header/footer which is of no use as I have to
fit in with pre-printed stationery.

Please help!!!

[David McRitchie]

Hi Mark,
Here is a macro that Myrna & Dana worked out a few days ago for someone.
That person wanted to start on page zero, so I'll comment out that line and
substitute. Since you did not post in the programming newsgroup, you may
not know how to install a macro. The name of this macro is Demo you can
change that to any name.
see orange colored area in
http://members.aol.com/dmcritchie/excel/formula.htm

This macro essentially prints one page at a time in the For / Next loop.
You must use the macro to print your document because there is only one cell
in the entire spreadsheet that contains the page number and it changes as
you print. Exactly what you asked for.

Sub Demo()
Dim TotalPages As Long
Dim pg As Long

TotalPages = ExecuteExcel4Macro("Get.Document(50)")
For pg = 1 To TotalPages
With ActiveSheet
'If pg > 1 Then .Range("D5").Value = pg - 1
If pg > 1 Then .Range("D5").Value = pg & " of " & TotalPages
.PrintOut From:=pg, To:=pg
End With
Next pg
End Sub

Hope this Helps, you can thank Dana for the concept, which is really
remarkable, and Myrna for catching some of the missing details in the
original poster's request.

David McRitchie
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

mark <ma...@bowood.u-net.com> wrote in message
news:379C8919...@bowood.u-net.com...

[Myrna Larson]

On Mon, 26 Jul 1999 22:22:55 -0400, "David McRitchie" <DMcRi...@msn.com>
wrote:

>you can thank Dana for the concept, which is really
>remarkable

I agree. Those old XLM macro functions are really far back in my mind. But they
can get you out of all sorts of jams like this problem of how many pages there
are in the printed document. I'm really surprised there's no VBA command for
this.

Anyway, if you want the page number on all pages, it needs one more change to
get rid of the part that excludes page 1:

Sub Demo()
Dim TotalPages As Long
Dim pg As Long

TotalPages = ExecuteExcel4Macro("Get.Document(50)")
For pg = 1 To TotalPages
With ActiveSheet

.Range("D5").Value = pg & " of " & TotalPages '<<< CHANGE HERE

[ernesto....@gmail.com]

I like this macro but how do you start from the first page and I ran it and it would change the current page number it would make it equal to the total pages.
Here is the one I am using.

Sub Pages()

Dim TotalPages As Long
Dim pg As Long


TotalPages = ExecuteExcel4Macro("Get.Document(50)")
For pg = 1 To TotalPages
With ActiveSheet

.Range("F1").Value = pg & " _ " & TotalPages