Page # of #

28 views
Skip to first unread message

Scott Dubuisson

unread,
Dec 16, 1999, 3:00:00 AM12/16/99
to
I have a worksheet that has multiple pages in it. I am using page setup
properties to repeat the top 18 rows at the top of each page. I am
trying to place a formula within that section that returns page # of #
for that worksheet and maybe the workbook? Will a formula or function
do this?
Thanks
Scott


Myrna Larson

unread,
Dec 16, 1999, 3:00:00 AM12/16/99
to
On Thu, 16 Dec 1999 10:18:04 -0600, Scott Dubuisson <icee...@airmail.net>
wrote:

No, it won't. You have to print the workbook one sheet at a time -- with a
macro -- setting the page number in the appropriate cell before each sheet is
printed. Something similar to the code below (untested, but should be close)
should do it. I assumed you have two named ranges, TotalPages and PageNum, to
receive the values. Note that this assumes the worksheet is only one page wide
but multiple pages tall. It that isn't true, the code would have to be
modified to put the page numbers farther to the right on the subsequent pages,
and you would have to work out the logic of where those cells are for each
printed page. That, in turn, depends on whether you are printing down-then
across, or across-then down.

Sub PrintWithPgNumInTitleRow()
Dim NumPages As Long, Pg As Long

NumPages = ExecuteExcel4Macro("Get.document(50,"")")

ActiveSheet.Range("TotalPages").Value = NumPages
For Pg = 1 To NumPages
With ActiveSheet
.Range("PageNum").Value = Pg
.PrintOut from:=Pg, to:=Pg
End With
Next Pg
End Sub


David McRitchie

unread,
Dec 16, 1999, 3:00:00 AM12/16/99
to
Myrna posted a solution as you asked for, but it would be a lot
more efficient if you were to simply use Page # of ## in the header or footer,
rather than in the spreadsheet.

As that would be preferable to invoking a print for one page at a time,
if that is an option.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm


Bernard Liengme

unread,
Dec 16, 1999, 3:00:00 AM12/16/99
to
Scott,
Just a thought: can you not use a footer or header and get Page # ot #
using the codes provided in headers and footers?
Bernard
bliengme.vcf

Bernard Liengme

unread,
Dec 16, 1999, 3:00:00 AM12/16/99
to
Scott,
Just a thought: can you not use a footer or header and get Page # ot #
using the codes provided in headers an footers?
Bernard
bliengme.vcf
Reply all
Reply to author
Forward
0 new messages