Putting Page Number in last page text

[DRK]

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

[Jim Rech]

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" <[email protected]> wrote in message
news:[email protected]...
|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

[DRK]

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" <[email protected]> wrote in message
> news:[email protected]...
> |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
>
>
>

[Jim Rech]

>> 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" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
| > news:[email protected]...
| > |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
| >
| >
| >

[DRK]

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" <[email protected]> wrote in message
> news:[email protected]...
> | 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" <[email protected]> wrote in message
> | > news:[email protected]...
> | > |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
> | >
> | >
> | >
>
>
>

[Linker IT Software]

Hi Dirk,

I have just added a function to my litLIB addin to achieve this. You can
have a look at the addin here: 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