locked
Number of Pages RRS feed

  • Question

  • I need to determine the number of pages that will be printed from a workbook to determine if I need to substitute a blank page to keep it at an even number of pages.  My problem is that this macro will run on a number of different workbooks, selecting different worksheets all of the time.  Is there a way to determine the number of pages that will print based off of selected pages?

    When the print dialog box pops up it knows...is it possible to pull that number?

    Friday, July 30, 2010 3:45 PM

Answers

  • This snippet will give you the # of pages for the selected range to be printed.

    'if the worksheet is not protected get PRINTED PAGES
    DIM lngHPages as Long
    DIM lngVPages as Long
    DIM lngTotalPages as Long

    If ActiveSheet.ProtectContents = False Then
      lngHPages = ActiveSheet.HPageBreaks.Count + 1
      lngVPages = ActiveSheet.VPageBreaks.Count + 1
      lngTotalPages = lngHPages * lngVPages
      MSGBOX lngTotalPages
    End If

    Hope this helps.
    Gary Brown


    Gary Brown
    • Proposed as answer by Kinneson Friday, July 30, 2010 7:43 PM
    • Marked as answer by in2thecode Monday, August 2, 2010 1:27 PM
    Friday, July 30, 2010 7:43 PM

All replies

  • This snippet will give you the # of pages for the selected range to be printed.

    'if the worksheet is not protected get PRINTED PAGES
    DIM lngHPages as Long
    DIM lngVPages as Long
    DIM lngTotalPages as Long

    If ActiveSheet.ProtectContents = False Then
      lngHPages = ActiveSheet.HPageBreaks.Count + 1
      lngVPages = ActiveSheet.VPageBreaks.Count + 1
      lngTotalPages = lngHPages * lngVPages
      MSGBOX lngTotalPages
    End If

    Hope this helps.
    Gary Brown


    Gary Brown
    • Proposed as answer by Kinneson Friday, July 30, 2010 7:43 PM
    • Marked as answer by in2thecode Monday, August 2, 2010 1:27 PM
    Friday, July 30, 2010 7:43 PM
  • That did the trick. Thank you so much.
    Monday, August 2, 2010 1:28 PM