Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Worksheet Page Numbers (2000)

    I have searched in vain for a solution, but to no avail. I would like to print on each worksheet page, the page number of that worksheet and the title (that would be inserted by me). For example, if worksheet no. 1 consists of 1 page, the page number (to be inserted at a location of my choice) would be "worksheet no. 1 (or whatever title I chose), page 1 of 1". If, on the other hand, worksheet no. 2 consisted of 2 pages, the first page would be "worksheet no. 2, page 1 of 2", and the second page would be "worksheet no. 2, page 2 of 2".
    Any help?
    Thanks,
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet Page Numbers (2000)

    Have you tried the Header/Footer tab in the File/Page Setup.... dialog? You can select several built-in formats from the dropdown list, or customize the header or footer; there are buttons for inserting the sheet name, page number and total number of pages.

    Note: the total number of pages is calculated for the current print job, not for the worksheet being printed. So if you print one worksheet, the total will be correct, but if you print several worksheets at once, the total will be the sum of the number of pages of the worksheets.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Page Numbers (2000)

    "Note: the total number of pages is calculated for the current print job, not for the worksheet being printed. So if you print one worksheet, the total will be correct, but if you print several worksheets at once, the total will be the sum of the number of pages of the worksheets."

    Yep-been there, done that. This is the rerason for my post.
    Thanks anyway, Hans

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet Page Numbers (2000)

    You could create a macro that prints out each sheet separately, so that the page count will refer to that sheet only.

    To print all worksheets in the active workbook:

    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.WorkSheets
    sh.PrintOut
    Next

    To print all selected worksheets (i.e. sheets whose tabs have been selected):

    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Windows(1).SelectedSheets
    sh.PrintOut
    Next

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Page Numbers (2000)

    Thanks, Hans. A couple of questions:

    How and what would be the title of each worksheet?
    Would they (the individual worksheets) print "page x of y", where "x" is the current page number, and "y" is the total number of pages in the worksheet?

    Thanks,

    Jeff

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet Page Numbers (2000)

    You can set the title in the File/Page Setup... dialog. If you click the sheet tab button in the custom header or footer dialog, Excel will insert a code that evaluates to the text on the sheet tab on printing (so if your sheet is named Sheet1, that will be used as title; if you edit the sheet tab to read "2003 Sales", *that* will be used. You can also type in your own text - this will be constant and independent of the sheet name.

    Using sh.Printout (as in the code example in my previous reply) is equivalent to selecting a single worksheet and printing it; so if you have put the codes for Page # of ## in the header or footer, the numbers will relate to that worksheet only.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Page Numbers (2000)

    Hans,
    Works just fine. I wasn't aware of the "Tab" feature.
    Thanks again,
    Jeff

Posting Permissions

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