Print summary pages

Question

There is a main page with this macro assigned to a button. Once the button is pressed it asks if you wish to continue and mentions it takes a while to run the macro. I'm looking to speed it up some if possible.

Sub Picture11_Click() 
Dim sht As Worksheet

If MsgBox("Do you wish to print summary pages? Will take approximately 1-2 minutes to run the macro", vbYesNo) = vbNo Then Exit Sub

Application.Calculation = xlCalculationManual 
Application.ScreenUpdating = False    
Application.Dialogs(xlDialogPrinterSetup).Show

PrintSummaryArray = Array(Sheet10.Name, Sheet11.Name, Sheet12.Name, Sheet16.Name, Sheet2.Name, Sheet8.Name, Sheet9.Name)

    For Each sht In Worksheets(PrintSummaryArray)
       sht.PageSetup.Zoom = False
       sht.PageSetup.FitToPagesWide = 1
       sht.PageSetup.FitToPagesTall = 1
       sht.PageSetup.BlackAndWhite = False
       sht.PageSetup.PrintArea = "A1:X62"
    Next
     Sheets(PrintSummaryArray).Select

ActiveWindow.SelectedSheets.PrintOut

Worksheets(Sheet1.Name).Select

Application.ScreenUpdating = True 
Application.Calculation = xlCalculationAutomatic

End Sub

Answer 1

I don't think this can be sped up

It's *possible* that selecting all the pages and printing-as-one is noticeably slower than printing each page iteratively (I recommend you try it out and see), but probably not by a significant amount.

The printer does take a certain amount of time to perform a print operation and that can't be optimised away.

---

Other Thoughts on your code

---

Codenames

Sheet10, Sheet11, Sheet12 etc. are codenames. They can be changed in the VBE by clicking on a sheet in the properties window. Like so:

The (name) property is the codename. It should be changed to something descriptive and useful e.g.

wsFrontpage, wsInitialSummary, wsSomethingAnalysis, wsOtherAnalysis, wsConclusions

And now you won't have to keep trying to remember which one was sheet11, which one was sheet13 etc.

---

Use With

With let's you hold an object reference, so this:

   sht.PageSetup.Zoom = False
   sht.PageSetup.FitToPagesWide = 1
   sht.PageSetup.FitToPagesTall = 1
   sht.PageSetup.BlackAndWhite = False
   sht.PageSetup.PrintArea = "A1:X62"

Becomes:

With sht.PageSetup
   .Zoom = False
   .FitToPagesWide = 1
   .FitToPagesTall = 1
   .BlackAndWhite = False
   .PrintArea = "A1:X62"
End With

Much easier to work with.

---

Only Exit In One Place

If you've got a random Exit Sub halfway through your code, on the end of a long if statement, it's very easy for someone to miss it. When you miss something like that, it's easy to change the code in a way that you *think* is safe, but is actually going to cause unexpected behaviour.

So, instead of using an Exit here, you should just wrap everything in an If statement:

Sub Picture11_Click()

    Const USER_PRINT_DIALOG As String = "Do you wish to print summary pages? Will take approximately 1-2 minutes to run the macro"

    If MsgBox(USER_PRINT_DIALOG, vbYesNo) = vbYes Then

        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .Dialogs(xlDialogPrinterSetup).Show
        End With

        PrintSummaryArray = Array(Sheet10.Name, Sheet11.Name, Sheet12.Name, Sheet16.Name, Sheet2.Name, Sheet8.Name, Sheet9.Name)

        Dim sht As Worksheet
        For Each sht In Worksheets(PrintSummaryArray)
           sht.PageSetup.Zoom = False
           sht.PageSetup.FitToPagesWide = 1
           sht.PageSetup.FitToPagesTall = 1
           sht.PageSetup.BlackAndWhite = False
           sht.PageSetup.PrintArea = "A1:X62"
        Next

        Sheets(PrintSummaryArray).Select
        ActiveWindow.SelectedSheets.PrintOut

        Sheet1.Select

        With Application
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End With

    End If

End Sub

---

Refactor Everything

Right now, your Picture11_Click Event only has to handle printing a specific set of worksheets. What if you want to do something else as well before/after? What if you want to print your sheets from another button somewhere else?

Picture11_Click is an Event Handler. It should not contain business logic. You should move your *Print Sheets* Logic into an appropriately named Sub and then call that from the event handler. Like so:

Option Explicit

Sub Picture11_Click()

    Const USER_PRINT_DIALOG As String = "Do you wish to print summary pages? Will take approximately 1-2 minutes to run the macro"

    If MsgBox(USER_PRINT_DIALOG, vbYesNo) = vbYes Then

        PrintThingyReport

    End If

End Sub

Public Sub PrintThingyReport()

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .Dialogs(xlDialogPrinterSetup).Show
    End With

    PrintSummaryArray = Array(Sheet10.Name, Sheet11.Name, Sheet12.Name, Sheet16.Name, Sheet2.Name, Sheet8.Name, Sheet9.Name)

    Dim sht As Worksheet
    For Each sht In Worksheets(PrintSummaryArray)
       sht.PageSetup.Zoom = False
       sht.PageSetup.FitToPagesWide = 1
       sht.PageSetup.FitToPagesTall = 1
       sht.PageSetup.BlackAndWhite = False
       sht.PageSetup.PrintArea = "A1:X62"
    Next

    Sheets(PrintSummaryArray).Select
    ActiveWindow.SelectedSheets.PrintOut

    Sheet1.Select

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

And now you can call that method from as many places as you like.