Putting Page Number in last page text
 

I have a workbook which uses a print macro. The printout is for several
worksheets which have variable ranges or even entire pages being
included/excluded based on Booleans.

On the last page of the printout I need to include the page number for the
last page before this legal notice and then subtract 2 and then include that
value on the last page of the legal notice (which is fixed at 2 pages in
length).

For example - my printout takes 20 pages before I get to the legal notice. I
have to capture that value of 20 and put it in a footnote on page 22 that
reads "The attached document totals 20 pages excluding this acknowledgment."
--
DRK

Putting Page Number in last page text
 

I'm not sure if this will help you but to get the number of pages that will
print with the current page setup you can use this code:

Pages = Application.ExecuteExcel4Macro("get.document(50)")

--
Jim
"DRK" wrote in message
...
|I have a workbook which uses a print macro. The printout is for several
| worksheets which have variable ranges or even entire pages being
| included/excluded based on Booleans.
|
| On the last page of the printout I need to include the page number for the
| last page before this legal notice and then subtract 2 and then include
that
| value on the last page of the legal notice (which is fixed at 2 pages in
| length).
|
| For example - my printout takes 20 pages before I get to the legal notice.
I
| have to capture that value of 20 and put it in a footnote on page 22 that
| reads "The attached document totals 20 pages excluding this
acknowledgment."
| --
| DRK

Putting Page Number in last page text
 

I do use a macro to set up the print ranges so perhaps within each macro
I could have a local variable and then sum the separate variables to get
my page count.

Yeah, the code I posted is old Excel 4 macro stuff (because VBA does not
include an equivalent) that only works on the active sheet. So you'd have
to activate each sheet and run the code, accumulating the grand total. That
grand total world have to go into your custom footer for the last page. You
couldn't use "Page &[Page] of &[Pages]" since Excel just uses its page
count, not yours. You might have to do more than one print job. Messy.


--
Jim
"DRK" wrote in message
...
| As I am building this document from multiple worksheets, it doesn't seem
to
| work. To print I use:
| Sheets(Array("Cover", "AboutThisIllustration",
"PolicyValuesLedger", _
| "PolicyValuesLedgerGuarG", "PolicyValuesLedgerGuarB")).Select
| ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
|
| So the document is already printed before it gets to your solution. The
last
| three items in the above array can range in number of pages from 1 to 6. I
do
| use a macro to set up the print ranges so perhaps within each macro I
could
| have a local variable and then sum the separate variables to get my page
| count.
|
| Right now in the page footer I'm using Page &[Page] of &[Pages]
|
| Any way of capturing that from whatever was the last page printed?
| --
| DRK
|
|
| "Jim Rech" wrote:
|
| I'm not sure if this will help you but to get the number of pages that
will
| print with the current page setup you can use this code:
|
| Pages = Application.ExecuteExcel4Macro("get.document(50)")
|
| --
| Jim
| "DRK" wrote in message
| ...
| |I have a workbook which uses a print macro. The printout is for several
| | worksheets which have variable ranges or even entire pages being
| | included/excluded based on Booleans.
| |
| | On the last page of the printout I need to include the page number for
the
| | last page before this legal notice and then subtract 2 and then
include
| that
| | value on the last page of the legal notice (which is fixed at 2 pages
in
| | length).
| |
| | For example - my printout takes 20 pages before I get to the legal
notice.
| I
| | have to capture that value of 20 and put it in a footnote on page 22
that
| | reads "The attached document totals 20 pages excluding this
| acknowledgment."
| | --
| | DRK
|
|
|

Putting Page Number in last page text
 

OK. I got it to work. I had a subroutine for each page to be printed. Each
routine set print ranges and/or hid/unhid columns or rows. So in each routine
I added the current routines total pages as reported by the
Application.ExecuteExcel4Macro("get.document(50)")

One happy camper. Thanks for your help.

--
DRK


"Jim Rech" wrote:

I do use a macro to set up the print ranges so perhaps within each macro
I could have a local variable and then sum the separate variables to get
my page count.

Yeah, the code I posted is old Excel 4 macro stuff (because VBA does not
include an equivalent) that only works on the active sheet. So you'd have
to activate each sheet and run the code, accumulating the grand total. That
grand total world have to go into your custom footer for the last page. You
couldn't use "Page &[Page] of &[Pages]" since Excel just uses its page
count, not yours. You might have to do more than one print job. Messy.


--
Jim
"DRK" wrote in message
...
| As I am building this document from multiple worksheets, it doesn't seem
to
| work. To print I use:
| Sheets(Array("Cover", "AboutThisIllustration",
"PolicyValuesLedger", _
| "PolicyValuesLedgerGuarG", "PolicyValuesLedgerGuarB")).Select
| ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
|
| So the document is already printed before it gets to your solution. The
last
| three items in the above array can range in number of pages from 1 to 6. I
do
| use a macro to set up the print ranges so perhaps within each macro I
could
| have a local variable and then sum the separate variables to get my page
| count.
|
| Right now in the page footer I'm using Page &[Page] of &[Pages]
|
| Any way of capturing that from whatever was the last page printed?
| --
| DRK
|
|
| "Jim Rech" wrote:
|
| I'm not sure if this will help you but to get the number of pages that
will
| print with the current page setup you can use this code:
|
| Pages = Application.ExecuteExcel4Macro("get.document(50)")
|
| --
| Jim
| "DRK" wrote in message
| ...
| |I have a workbook which uses a print macro. The printout is for several
| | worksheets which have variable ranges or even entire pages being
| | included/excluded based on Booleans.
| |
| | On the last page of the printout I need to include the page number for
the
| | last page before this legal notice and then subtract 2 and then
include
| that
| | value on the last page of the legal notice (which is fixed at 2 pages
in
| | length).
| |
| | For example - my printout takes 20 pages before I get to the legal
notice.
| I
| | have to capture that value of 20 and put it in a footnote on page 22
that
| | reads "The attached document totals 20 pages excluding this
| acknowledgment."
| | --
| | DRK
|
|
|

Putting Page Number in last page text
 

Hi Dirk,

I have just added a function to my litLIB addin to achieve this. You can
have a look at the addin he www.oraxcel.com/projects/litlib.

I have added the formula =PageNumber and =PageCount

Hope this helps,

Gerrit-Jan Linker
Linker IT Software
www.oraxcel.com

On the last page of the printout I need to include the page number for the
last page before this legal notice and then subtract 2 and then include
that
value on the last page of the legal notice (which is fixed at 2 pages in
length).

For example - my printout takes 20 pages before I get to the legal notice.
I
have to capture that value of 20 and put it in a footnote on page 22 that
reads "The attached document totals 20 pages excluding this
acknowledgment."
--
DRK