Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Sequential Page Numbering

    The model I'm working on has multi-page and single page worksheet printouts which are printed sequentially as separate print jobs, via macro. Is there a way to determine the total pages from the previous print job so that the next print job page number can be set sequentially.

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Page Numbering

    Why can't you print them all at the same time? ie hold shift and select each sheet to print and then print

    (sorry if I missed the point)

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sequential Page Numbering

    Tks for your reply. The simplest solution is always the preferred one. However, I have the model set up so that within a worksheet, the output pages do not necessarily appear sequentially; e.g., a page of calculations may have an explanations page to the right, followed by several calculation pages beneath the first one.

    What I had in mind was to somehow reference the &N value (Total Pages) available with Excel footer through VBA. Is this possible?

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sequential Page Numbering

    If you select the sheets to be printed (ctrl-click for non-adjacent sheets) the total number of pages will calculate correctly so the "page X of Y" will come out properly if you have that set in the footer.

    If you do the equivalent action in VBA by adding the required sheets to the selection and then printing the selection, I imagine it would work properly, although I have not tried it. I'm off to waste a couple of sheets of paper - I'll let you know!

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sequential Page Numbering

    Yup - it seems to work.

    Try something like:

    <pre> Sheets(Array("Summary", "Registration", "Memberships", "Equipment")).Select
    Sheets("Summary").Activate
    ActiveWindow.SelectedSheets.PrintOut
    </pre>

    obviously, you'll have to change the sheetnames to match your workbook!

    and you should get what you need.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Page Numbering

    Or you could also refer to sheets by number:

    <pre>Sheets(Array(1,3,4,7)).Select
    sheets(1).Activate
    </pre>


    It might depend on whether you're more likely to rename a sheet, or to move/insert a sheet.

    Referring by sheet number makes the code easier to use in another workbook.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sequential Page Numbering

    I suppose I find that I am more likely to move a sheet than to rename it! Even if not, I think that using sheet names is preferable, since it is obvious what you are referring to; a user six months later looking at the code (say to add a sheet to the standard output) will see the names and understand the purpose, where the reference by index number may not tip them off....

    If the code is to be used in another workbook, I would almost prefer that the sheetnames be used, since then I KNOW that the code won't work, and will generate an error (subscript out of range, I expect), rather than 'sort of' working by printing sheets other than the ones I want (I can't imagine that I would want the first, third, fourth and seventh sheets printed in two otherwise-unrelated workbooks!). Since I will have to modify the code anyway, I might as well set myself the task of looking for something meaningful to me, rather than to the computer.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Page Numbering

    As far as I know, the only way to really determine the page count programmatically is to use the Excel 4 (XLM) Get.Document macro function. You can execute this macro function from VBA. Here you have some code that loops through all the worksheets in the active workbook and counts the number of pages.

    Sub TotalNumberOfPages()
    Dim Total As Integer
    Dim Sht As Worksheet
    Dim pg As Integer
    Total = 0
    For Each Sht In Worksheets
    Sht.Activate
    pg = ExecuteExcel4Macro("Get.Document(50)")
    Total = Total + pg
    Next Sht
    MsgBox "Total Number of Pages to be printed = " & Str$(Total)
    End Sub

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sequential Page Numbering

    Thanks for the example. I used the Excel4 macro GetDocument to update the first page number for the next printout. Tks to all who replied.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •