Get a Total Number of Pages for Excel Workbook

This Code Calculates the Total Number of pages to be printed on an excel workbook as well as each sheet num of pages:


Sub NumberOfPrintedPages()
    numpages = 0
    sht1 = 0
    sht2 = 0
    sht3 = 0
   
For i = 1 To Worksheets.Count
    Worksheets(i).DisplayAutomaticPageBreaks = True
    HorizBreaks = Worksheets(i).HPageBreaks.Count
    hpages = HorizBreaks + 1
    ‘VertBreaks = Worksheets(i).VPageBreaks.Count
    ‘VPages = VertBreaks + 1
    numpages = numpages + hpages
    Worksheets(i).DisplayAutomaticPageBreaks = False
   
    Select Case i
        Case 1
            sht1 = hpages
        Case 2
            sht2 = hpages
        Case 3
            sht3 = hpages
    End Select
   
Next
    ‘Cells(1, 1) = numpages
    MsgBox numpages & ” Pages in This Work Book” & vbCr & sht1 & ” Pages in Sheet1″ & vbCr & sht2 & ” Pages in Sheet2″ & vbCr & sht3 & ” Pages in Sheet3″
   
End Sub
Advertisements

Posted on May 4, 2010, in Excel, VBA. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: