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!!!
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...
>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