Print Pages Count

[spilner]

hi,is there anyway i can know how much pages will be printed when i select a group sheets.
some of sheets i got about up to 9 pages.
thanks for your help.

 

[Trebor76]

Hi spilner,

The following will assign the number of pages to be printed to the 'intNumOfPrintPages' varaible (which I have simply displayed in a message box, but you can use it as you please):

Sub Macro2()

    Dim intNumOfPrintPages As Integer

    intNumOfPrintPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    
    MsgBox intNumOfPrintPages

End Sub

HTH

Robert

 

[spilner]

its not working,when i select the range of sheets.the macro still tell me 1 only.

 

[Trebor76]

Ah, using this method you need to select each (visible) tab and increment the counter by the number of printouts for that tab, i.e.

Sub Macro2()

    Dim strStartTab As String, _
        strStartCell As String
    Dim intNumOfPrintPages As Integer
    
    Application.ScreenUpdating = False
    
    strStartTab = ActiveSheet.Name
    strStartCell = ActiveCell.Address
    
    For Each Worksheet In ThisWorkbook.Sheets
    
        If Worksheet.Visible = True Then
    
            With Worksheet
                .Select
                intNumOfPrintPages = intNumOfPrintPages + .Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
            End With
        
        End If
        
    Next Worksheet
    
    Sheets(strStartTab).Select
    Range(strStartCell).Select
    
    Application.ScreenUpdating = True
    
    MsgBox intNumOfPrintPages

End Sub

HTH

Robert

 

[spilner]

i just select 3 sheets and run the macro.i dont know why it take so long to dertermine the pages.its still proccessing right now..about 1 min..

 

[Trebor76]

i dont know why it take so long to dertermine the pages

Me either

It may be due to the fact the command is an old V4.0 macro which isn't as efficient as newer based macros, but it's most accurate way I've found to count the number of page(s) to be printed.

 

[spilner]

Me either

It may be due to the fact the command is an old V4.0 macro which isn't as efficient as newer based macros, but it's most accurate way I've found to count the number of page(s) to be printed.

probably theres somethin need to fix in the code,i dont get the result tho after 10 mins wait.
i bet u can figure it out.
i google everything about it,i cant find good one tho.
im using excel 2010.thanks for trying man.

 

[spilner]

can someone else help this?

 

[Trebor76]

See if this is any better:

Option Explicit
Sub Macro2()

    Dim blnCalcMethod As Boolean
    Dim strStartTab As String, _
        strStartCell As String
    Dim wrkTab As Worksheet
    Dim intNumOfPrintPages As Integer
    Dim objTab As Object
    
    With Application
        .ScreenUpdating = False
        If .Calculation = xlCalculationAutomatic Then
            blnCalcMethod = True
            .Calculation = xlCalculationManual
        End If
    End With
        
    strStartTab = ActiveSheet.Name
    strStartCell = ActiveCell.Address
    
    For Each wrkTab In ThisWorkbook.Sheets
    
        If wrkTab.Visible = True Then
        
            Set objTab = Sheets(wrkTab.Name)
            
            With objTab
                .Select
                intNumOfPrintPages = intNumOfPrintPages + .Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
            End With
        
        End If
        
    Next wrkTab
    
    Sheets(strStartTab).Select
    Range(strStartCell).Select
    
    With Application
        .ScreenUpdating = True
        If blnCalcMethod = True Then
            .Calculation = xlCalculationAutomatic
        End If
    End With
    
    MsgBox intNumOfPrintPages

End Sub