none
Number of pages to be printed within a specified print range using VBA

    Question

  • I have a question relating on how to determine the number of pages that will be printed within a specified print range.

    Put simply (the overall process is more complex that I relate to here), I can control the area of a spreadsheet that I want to print (which will be different than the automated print area that Excel will produce), however I need to be able to determine if any given print area will print out on more than one page. I cannot use the funcationality to fit to one page as this will affect the look of those pages affected (if any given area is printing out on more than page then I need to take action withn the code).

    I'm new to using the forum in this way so if anyone can assist and if a reply is forthcoming could it be sent to gmp@seriouslaw.co.uk or macalpine1@hotmail.co.uk

    Monday, October 08, 2012 9:23 AM

Answers

  • If you only want to know if more than one page will be print you could try this

    Sub test()
    Dim hPBcnt As Long, vPBcnt As Long
    Dim ws As Worksheet
         Set ws = ActiveSheet
         hPBcnt = ws.HPageBreaks.Count + 1
         vPBcnt = ws.VPageBreaks.Count + 1
            MsgBox hPBcnt * vPBcnt & " page(s)"
    
    End Sub

    Empty "pages" might not print, depending where they are in the print area, so the number of printed pages might be less.

    Peter Thornton

    Monday, October 08, 2012 9:54 AM
    Moderator

All replies

  • If you only want to know if more than one page will be print you could try this

    Sub test()
    Dim hPBcnt As Long, vPBcnt As Long
    Dim ws As Worksheet
         Set ws = ActiveSheet
         hPBcnt = ws.HPageBreaks.Count + 1
         vPBcnt = ws.VPageBreaks.Count + 1
            MsgBox hPBcnt * vPBcnt & " page(s)"
    
    End Sub

    Empty "pages" might not print, depending where they are in the print area, so the number of printed pages might be less.

    Peter Thornton

    Monday, October 08, 2012 9:54 AM
    Moderator
  • You can use

    ActiveSheet.PageSetup.Pages.Count


    Regards, Hans Vogelaar

    Monday, October 08, 2012 10:02 AM
  • ActiveSheet.PageSetup.Pages.Count

    Yes that would be simpler than looking at page-breaks, but note ".Pages" was introduced in Excel 2007 so would fail in Excel 2003. The actual count might be less for the same reason I explained earlier.

    Peter Thornton

    Monday, October 08, 2012 10:16 AM
    Moderator