code_junkie

How to print faster in Excel VBA?

Nov 11th, 2011
95
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.13 KB | None | 0 0
  1. Application.ScreenUpdating = False
  2.  
  3. Dim origScreenUpdating As Boolean
  4. origScreenUpdating = Application.ScreenUpdating
  5. Application.ScreenUpdating = False
  6.  
  7. Dim origCalcMode As xlCalculation
  8. origCalcMode = Application.Calculation
  9. Application.Calculation = xlCalculationManual
  10.  
  11. With ActiveSheet.PageSetup
  12. If .PrintHeadings <> False Then .PrintHeadings = False
  13. If .PrintGridlines <> False Then .PrintGridlines = False
  14. If .PrintComments <> xlPrintNoComments Then .PrintComments = xlPrintNoComments
  15. ' Etc...
  16. End With
  17.  
  18. Application.ScreenUpdating = origScreenUpdating
  19. Application.Calculation = origCalcMode
  20.  
  21. Public Sub CopyPageSetupToAll(ByRef SourceSheet As Worksheet)
  22. ' Raise error if invalid source sheet is passed to procedure
  23. '
  24. If (SourceSheet Is Nothing) Then
  25. Err.Raise _
  26. Number:=vbErrorObjectVariableNotSet, _
  27. Source:="CopyPageSetupToAll", _
  28. Description:="Unable to copy Page Setup settings: " _
  29. & "invalid reference to source sheet."
  30. Exit Sub
  31. End If
  32.  
  33. SourceSheet.Activate
  34.  
  35. With SourceSheet.PageSetup
  36. ' ...
  37. ' place PageSetup customizations here
  38. ' ...
  39. End With
  40.  
  41. SourceSheet.Parent.Worksheets.Select
  42. Application.SendKeys "{ENTER}", True
  43. Application.Dialogs(xlDialogPageSetup).Show
  44. End Sub
  45.  
  46. Public Sub CopyPageSetupToAll(ByRef SourceBook As Workbook)
  47. Dim tempSheet As Worksheet
  48.  
  49. ' Raise error if invalid workbook is passed to procedure
  50. '
  51. If (SourceBook Is Nothing) Then
  52. Err.Raise _
  53. Number:=vbErrorObjectVariableNotSet, _
  54. Source:="CopyPageSetupToAll", _
  55. Description:="Unable to copy Page Setup settings: " _
  56. & "invalid reference to source workbook."
  57. Exit Sub
  58. End If
  59.  
  60. Set tempSheet = SourceBook.Worksheets.Add
  61.  
  62. tempSheet.Activate
  63.  
  64. With tempSheet.PageSetup
  65. ' ...
  66. ' place PageSetup customizations here
  67. ' ...
  68. End With
  69.  
  70. SourceBook.Worksheets.Select
  71. Application.SendKeys "{ENTER}", True
  72. Application.Dialogs(xlDialogPageSetup).Show
  73. tempSheet.Delete
  74.  
  75. Set tempSheet = Nothing
  76. End Sub
Add Comment
Please, Sign In to add comment