Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consecutive Page Numbering - Pages out of order (2

    Hi,

    I'm trying to write something that prints a set of sheets in an order different from the one they appear in in the workbook (for example, left to right in the workbook they might be X, Y and Z, but need to be printed Z,X,Y). It needs to print them with consecutive page numbering for all printed pages.

    The problem with sending them one by one, each with page numbers starting at the appropriate number, is that often the sheets will have Fit to set to 1 x "" at page setup, or multiple large print areas, so I can't ascertain the number of pages that any sheet printed on from there. Is there an easier way to consecutively number the printed sheets than to temporarily reorder them in the workbook?

    Thanks!

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Consecutive Page Numbering - Pages out of order (2

    I would be inclined to place the various areas on separate sheets, then arrange these sheets in the desired order; group them then print.
    Regards
    Don

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consecutive Page Numbering - Pages out of order (2

    If you can't reorder the sheets.

    How about print each sheet individually, and before you print each successive sheet, look to see what last page printed and manually set the start page of that worksheet to the next page

    Steve

  4. #4
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consecutive Page Numbering - Pages out of orde

    Thanks Don! That's the sort of thing that never occurs to me. I'm not sure if I will use it here, because the workbooks in question are so huge and taxing on Excel already, and so many sheets may be involved, but it is actually very helpful with another project I'm working on.
    Best,

  5. #5
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consecutive Page Numbering - Pages out of orde

    Thanks Steve. Hope I'm not being dense here - I tried to do the closest thing I could figure to what you suggested, which is to set up a do loop that prints each successive page 1,2,3, etc from a worksheet until that results in an error. But unfortunately it seems that asking Excel to print from page 24 to 24 on a worksheet that only has 12 pages to be printed doesn't generate an error. Is there some other way of accessing the name of that last printed sheet?
    Thanks.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consecutive Page Numbering - Pages out of orde

    I don't understand what you mean by the name of the that last printed sheet?

    If you are doing a macro, why not print each worksheet individually and start the next worksheet with the last page printed?

    Steve

  7. #7
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consecutive Page Numbering - Pages out of orde

    It's the prevalence of worksheets where Page Setup>Page>Fit to is set to 1 x "" that is causing the problem - I wanted to see if there was an easy way to access the total number of pages that such a sheet will print on.
    Basically just looking to see if there's an accessible property or simple workaround that gives one the number of pages that a sheet will print on. I guess, actually, it could probably be calculated from the h and vpagebreaks.
    Thanks again

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consecutive Page Numbering - Pages out of orde

    A line like:
    <pre>dim iPages as Integer
    iPages = ExecuteExcel4Macro("Get.Document(50)")</pre>


    will give the number of pages of the active sheet and store it into a variable

    This is the code I was alluding to. It will print the sheets in the order "Z", "Y", "X". Change as needed (you can even add more to the array.

    <pre>Option Explicit
    Sub PrintSheetOrdered()
    Dim iPages As Integer
    Dim iLastPage As Integer
    Dim i As Integer
    Dim vSheetOrder

    vSheetOrder = Array("z", "y", "X")
    iLastPage = 0
    For i = LBound(vSheetOrder) To UBound(vSheetOrder)
    Sheets(vSheetOrder(i)).Activate
    ' Get count of pages in active sheet
    iPages = ExecuteExcel4Macro("Get.Document(50)")

    ' Print worksheet
    With ActiveSheet
    'set page
    .PageSetup.FirstPageNumber = iLastPage + 1
    .PrintOut
    End With
    iLastPage = iLastPage + iPages
    Next
    End Sub</pre>


    Steve

  9. #9
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consecutive Page Numbering - Pages out of orde

    Thanks so much - this is perfect.

Posting Permissions

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