I have an userform that has check boxes where user can decide which worksheets to print.
Code in userform:
Sub PrintOK_Click() PrintSelection = Summary.value + _ CCSummary.value + _ DivSalaries.value + _ CCSalaries.value + _ PrintAll.value _ ' Displays warning box if no report is selected If PrintSelection = 0 Then MsgBox "No Report(s) selected. Please select Report(s) to print.", 0 + vbExclamation, "Incomplete Request" End If ' Check to see which worksheets have been selected and ' Run print modules for selected worksheets If Summary.value = -1 Then Call setup("Summary") End If If CCSummary.value = -1 Then Call setup("Monthly CC Sum") End If If DivSalaries.value = -1 Then Call setup("Div Sal Sum") End If If CCSalaries.value = -1 Then Call setup("CC Sal Sum") End If If PrintAll.value = -1 Then Call setup("PrintAll") End If ' Close Form box End End Sub
Userform selection is passed into the following. Takes the worksheet(s) selected and stores into array.
Sub setup(Optional tabs As String) 'store sheets into array Dim arr() As Variant 'called from printer icon on activesheet If tabs = "" Then arr = Array(ActiveSheet.Name) 'called via checkbox from print menu ElseIf tabs = "PrintAll" Then arr = Array("Summary", "Monthly CC Sum", "Div Sal Sum", "CC Sal Sum") Else arr = Array(tabs) End If Call goPrint(arr) End Sub
Which then passes the array to another sub to do the printing
Sub goPrint(tabArr As Variant) Dim ws As Worksheet Dim area As String Dim title As String 'loop through array to print each sheet For i = 0 To UBound(tabArr) Set ws = Worksheets(tabArr(i)) With ws If .Name = "Summary" Then area = "PRNSUMMARY" title = "$1:$9" ElseIf .Name = "Monthly CC Sum" Then area = "PRNMONTHLYCCSUM" title = "$2:$16" ElseIf .Name = "Div Sal Sum" Then area = "PRNDIVSALSUM" title = "" ElseIf .Name = "CC Sal Sum" Then area = "PRNCCSALSUM" title = "$2:$16" End If With .PageSetup .PrintArea = area .PrintTitleRows = title .PrintTitleColumns = "" .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = Year(Now()) & " Budget: &F" .CenterFooter = "Page &P of &N" .RightFooter = "&D: &T" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments '.PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False End With 'Debug.Print tabArr(i) 'Debug.Print area .PrintOut Copies:=1, Collate:=True End With Next End Sub
There's a delay of maybe two seconds after pressing OK before the printer starts to print. The previous (much longer) code had no discernible delay. How to improve speed?
(Previous code had a different sub for each worksheet thus repeating the
pagesetup code block multiple times.)