Hi,
I have a rather long macro that controls and limits the printing
process for the end user. It works okay but after running and you
return to the spreadsheet the screen is very slow to refresh as you
scroll around the worksheet.
The macro gives the user the choice of viewing a filtered set of
results on screen (Print_View_Option = "V") or printing out the result
(Print_View_Option = "V"). I think I've worked out that the issue
only occurs when the user tries to print out the results and the macro
adjusts the Page Setup.
I've done some research that suggests the Page Setup routine is
inherently slow but this doesn't explain the slowness AFTER the macro
has finished.
I've attached a slightly simplified version of the macro below that
still manifests the same problem.
Can anyone help?
Sub Print_Options2()
Dim RR As Object
Dim LastRow As Long
Dim ReportOrder As String
Dim OverallFilterType As String
Dim IndividualFilterType As String
Dim PaperSize As String
Dim ReportType As String
Set RR = ThisWorkbook.Sheets("Risk Register")
Print_View_Option = "P"
PaperSize = "A4"
ReportType = "Full Risk Register"
OverallFilterType = "A"
IndividualFilterType = "All Individual Control Assessments"
Application.ScreenUpdating = False
Application.EnableEvents = False
RR.Unprotect Password:=Password
On Error Resume Next
RR.ShowAllData
On Error GoTo 0
' Ensures any rows with wrapped text are expanded so that all text
is visible
LastRow = RR.Range("AD" & Rows.Count).End(xlUp).Row
RR.Rows("6:" & LastRow).EntireRow.AutoFit
' Hide rows with no data
Selection.AutoFilter Field:=30, Criteria1:="x"
If Application.Dialogs(xlDialogPrinterSetup).Show Then
RR.DisplayPageBreaks = False
With RR.PageSetup
If PaperSize = "A3" Then
.PaperSize = xlPaperA3
Else
.PaperSize = xlPaperA4
End If
.PrintArea = "$B:$AB"
.LeftFooter = _
"&""Arial,Bold""Print Criteria:&""Arial,Regular""" &
Chr(10) & _
" - " & ReportType & Chr(10) & " - " &
OverallFilterType & Chr(10) & _
" - " & IndividualFilterType
.RightFooter = RR.Range("K1").Value
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
' Show rows with no data
Selection.AutoFilter Field:=30
' When user has chosen to Print rather than View revert all
settings back to standard
If Print_View_Option = "P" Then
' Revert hidden columns to original state
If RR.Range("J2") = "Consolidation" Then
RR.Columns("A:A").EntireColumn.Hidden = True
RR.Columns("B:B").EntireColumn.Hidden = False
Else
RR.Columns("A:B").EntireColumn.Hidden = True
End If
RR.Columns("C:P").EntireColumn.Hidden = False
RR.Columns("Q:R").EntireColumn.Hidden = True
RR.Columns("S:V").EntireColumn.Hidden = False
RR.Columns("X:AB").EntireColumn.Hidden = False
RR.Columns("AC:CD").EntireColumn.Hidden = True
On Error Resume Next
RR.ShowAllData
On Error GoTo 0
End If
RR.Range("I3").Activate
RR.Protect Password:=Password, DrawingObjects:=True,
Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
Application.EnableEvents = True
Set RR = Nothing
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
