Macro became slow

[maremalin]

Hello,
i'm using macro to speed up printint to one page process as i print a big number of documents daily.
It worked good but for some reasons it takes considerably longer now to run - making it pointless to use.
Can anyone spot anything wrong with it:

Sub Macro1()
'

' Keyboard Shortcut: Ctrl+t
'
Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.166645835937175)
.RightMargin = Application.InchesToPoints(0.166645835937175)
.TopMargin = Application.InchesToPoints(0.166645835937175)
.BottomMargin = Application.InchesToPoints(0.166645835937175)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsNA
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub



Apprieciate any comments/ suggestions
Please let me know if you need more info

 

[VoG]

If you are using Excel 2010 or later try

Sub Macro1()
'

' Keyboard Shortcut: Ctrl+t
'
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.166645835937175)
.RightMargin = Application.InchesToPoints(0.166645835937175)
.TopMargin = Application.InchesToPoints(0.166645835937175)
.BottomMargin = Application.InchesToPoints(0.166645835937175)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsNA
End With
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintPreview
End Sub

 

[maremalin]

Hi sorry
should have said that
i use Excel 2007

 

[VoG]

Then, sorry there isn't much that you can do about the slowness. Excel communicates with the printer driver for every line of code that set the print properties.

 

[maremalin]

Then, sorry there isn't much that you can do about the slowness. Excel communicates with the printer driver for every line of code that set the print properties.

right
i have used this macro for few years and it worked spot on
It slowed down recently. I have not made ancy changes to PC
Do you think this could be related to printer's driver?

Just trying to get down to what could have caused that

 

[VoG]

If you (or Windows) have changed the printer driver that could have affected the speed of the macro.

 

[maremalin]

Thank you for your advice.
I have changed default printer and macro works like before!

thank you again

 

[Peter_SSs]

Thank you for your advice.
I have changed default printer and macro works like before!

thank you again

It's a bit fiddly but I have used what you have done manually in the macro in the past with older excel versions.

The process is ..

- Determine the current printer and store that info in a variable
- Have the macro change the current printer to one that doesn't have the speed problem
- Do the page setup
- Use the stored info to return the current printer to what is was at the start of the macro.

 

[maremalin]

Just to keep you up to date.
I have tried removing printer, updating driver but that didn't work
so i have decided to drastically simplyfy macro.

This is what works for me and it works FAST:

Sub Macro1()
'
'
' Keyboard Shortcut: Ctrl+t
'
Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
.Zoom = False
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub