Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Manual Page Numbering-VBA (97+)

    Hi all,

    There have been numerous posts about going thru all the sheets in a workbook, so something like
    Dim Sh As Worksheet
    Dim pagenum As Integer
    pagenum=0
    For Each Sh In Worksheets
    pagenum = pagenum + 1
    With Sh.PageSetupBurg"
    .RightFooter = "Page " & pagenum
    etc

    would allow me to loop thru all sheets and number them with a page number in the footer (or wherever) plus do whatever else I wanted for page setup.

    This works fine as long as 1 sheet fits on an output page. However, if the sheet requires multiple print pages, I would like to still have a "useful" set of page numbers. With the above, all pages resulting from a worksheet have the same number.

    So what I'd like to be able to do from VBA is number the pages with any of the following approaches:
    - 1-1, 1-2, 2-1, 2-2, 2-3 (m-n where m is the sheet per the above loop and n is the number of printed pages per sheet)
    - 1-A, 1-B, 2-A, 2-B, 2-C similar to above
    - 1, 2, 3, 4, 5 where 1 and 2 are the page numbers for the 2 printed pages resulting from sheet1 and 3-5 are the page numbers for the 3 printed pages resulting from sheet2

    Basically, the question might be asked as to how to determine how many printed pages will result from a single sheet.

    Thanks.

    Fred

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manual Page Numbering-VBA (97+)

    Even if you could figure out how many pages a worksheet would print (and I don't know how you could do that), you still have the problem that there is only one header and footer for each worksheet. You don't have anyplace to put the additional page numbers.
    Legare Coleman

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

    Re: Manual Page Numbering-VBA (97+)

    You can do it with an old Excel4 macro: see this thread.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Manual Page Numbering-VBA (97+)

    Hi Hans

    Thanks for the reply. Is there anything those Excel4 macros can't do? Any clues why MS didn't provide for their functionality going forward?

    OK, now we can find out how many printed pages result from a worksheet. As Legare suggested in his reply to me, the next trick would be to be able to create a loop so that one could change the header to have the correct page number per my original post (eg, 1-A, 1-B, 2-A, etc). Any ideas on that?

    Fred

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

    Re: Manual Page Numbering-VBA (97+)

    Have a look at this thread. Maybe you can combine both macros into one that does the job for you.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Manual Page Numbering-VBA (97+)

    Hans,

    I hope you got my email on the other thread on Incremented Footers. There were numerous questions there about your 2nd ref. I meant to post it here but was following your code there and forgot to post it on this thread.

    Fred

Posting Permissions

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