Print Macro runs "V E R Y S L O W"

G

Guest

Hi All.....

I recorded a Macro to print two ranges from a sheet. The first is printed
in Portrait view, and the second in Landscape. It works just fine, except
that it runs very very slowly, and since I have 12 of these to run in
succession it takes a long time......I tried just changing the PrintQuality
value from -4 to -1 but that just changed the quality of the printout and not
the speed of the macro.....can anybody suggest how I might speed this macro
up please?

Sub Print30210twosheets()

'***********************************************************
'This macro runs an individual Department Print MonthFiles macros
'***********************************************************

Sheets("Months").Select
'Print30210_1
Application.GoTo Reference:="Print30210_1"
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$48"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$48"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Printed by: crober"
.CenterFooter = ""
.RightFooter = _
"D:\Linvatec\QRB_Report\Oct2004NEW\QRBtest9\ChucksDPMcalcsMasterC.xls"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'Print30210_2
Application.GoTo Reference:="Print30210_2"
ActiveSheet.PageSetup.PrintArea = "$J$1:$AC$48"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$J$1:$AC$48"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Printed by: crober"
.CenterFooter = ""
.RightFooter = _
"D:\Linvatec\QRB_Report\Oct2004NEW\QRBtest9\ChucksDPMcalcsMasterC.xls"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Sheets("Main").Select
Range("A1").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3
 
J

Jim Rech

Page Setup commands can be terribly slow depending on the printer driver.
I've seen cases where a macro that runs in a second on one machine takes 10
minutes on another. The problem is that each line of the page setup, like
..RightHeader = "", makes a separate call to the printer driver.

So.. delete as many lines from this macro as you can. Only include the
things that have to change at print time and cannot be preset. For example,
preset the center footer if possible and then the macro does not have to
include it. Also, try to get the very latest driver for your printer.

--
Jim Rech
Excel MVP
| Hi All.....
|
| I recorded a Macro to print two ranges from a sheet. The first is printed
| in Portrait view, and the second in Landscape. It works just fine, except
| that it runs very very slowly, and since I have 12 of these to run in
| succession it takes a long time......I tried just changing the
PrintQuality
| value from -4 to -1 but that just changed the quality of the printout and
not
| the speed of the macro.....can anybody suggest how I might speed this
macro
| up please?
|
| Sub Print30210twosheets()
|
| '***********************************************************
| 'This macro runs an individual Department Print MonthFiles macros
| '***********************************************************
|
| Sheets("Months").Select
| 'Print30210_1
| Application.GoTo Reference:="Print30210_1"
| ActiveSheet.PageSetup.PrintArea = "$A$1:$H$48"
| With ActiveSheet.PageSetup
| .PrintTitleRows = ""
| .PrintTitleColumns = ""
| End With
| ActiveSheet.PageSetup.PrintArea = "$A$1:$H$48"
| With ActiveSheet.PageSetup
| .LeftHeader = ""
| .CenterHeader = ""
| .RightHeader = ""
| .LeftFooter = "Printed by: crober"
| .CenterFooter = ""
| .RightFooter = _
|
"D:\Linvatec\QRB_Report\Oct2004NEW\QRBtest9\ChucksDPMcalcsMasterC.xls"
| .LeftMargin = Application.InchesToPoints(0.25)
| .RightMargin = Application.InchesToPoints(0.25)
| .TopMargin = Application.InchesToPoints(0.5)
| .BottomMargin = Application.InchesToPoints(0.25)
| .HeaderMargin = Application.InchesToPoints(0.5)
| .FooterMargin = Application.InchesToPoints(0.5)
| .PrintHeadings = False
| .PrintGridlines = False
| .PrintComments = xlPrintNoComments
| .PrintQuality = -4
| .CenterHorizontally = True
| .CenterVertically = False
| .Orientation = xlPortrait
| .Draft = False
| .PaperSize = xlPaperLetter
| .FirstPageNumber = xlAutomatic
| .Order = xlDownThenOver
| .BlackAndWhite = False
| .Zoom = False
| .FitToPagesWide = 1
| .FitToPagesTall = 1
| End With
| ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
|
| 'Print30210_2
| Application.GoTo Reference:="Print30210_2"
| ActiveSheet.PageSetup.PrintArea = "$J$1:$AC$48"
| With ActiveSheet.PageSetup
| .PrintTitleRows = ""
| .PrintTitleColumns = ""
| End With
| ActiveSheet.PageSetup.PrintArea = "$J$1:$AC$48"
| With ActiveSheet.PageSetup
| .LeftHeader = ""
| .CenterHeader = ""
| .RightHeader = ""
| .LeftFooter = "Printed by: crober"
| .CenterFooter = ""
| .RightFooter = _
|
"D:\Linvatec\QRB_Report\Oct2004NEW\QRBtest9\ChucksDPMcalcsMasterC.xls"
| .LeftMargin = Application.InchesToPoints(0.25)
| .RightMargin = Application.InchesToPoints(0.25)
| .TopMargin = Application.InchesToPoints(0.5)
| .BottomMargin = Application.InchesToPoints(0.25)
| .HeaderMargin = Application.InchesToPoints(0.5)
| .FooterMargin = Application.InchesToPoints(0.5)
| .PrintHeadings = False
| .PrintGridlines = False
| .PrintComments = xlPrintNoComments
| .PrintQuality = -4
| .CenterHorizontally = True
| .CenterVertically = False
| .Orientation = xlLandscape
| .Draft = False
| .PaperSize = xlPaperLetter
| .FirstPageNumber = xlAutomatic
| .Order = xlDownThenOver
| .BlackAndWhite = False
| .Zoom = False
| .FitToPagesWide = 1
| .FitToPagesTall = 1
| End With
| ActiveWindow.SelectedSheets.PrintOut Copies:=1
|
| Sheets("Main").Select
| Range("A1").Select
|
| End Sub
|
|
| TIA
| Vaya con Dios,
| Chuck, CABGx3
 
G

Guest

Yup, that did the trick......I removed several lines as you suggested and it
cut the run time down from over 30 seconds per macro to about 10 seconds.
This is acceptable.

Thanks again muchly Jim,

Vaya con Dios,
Chuck, CABGx3
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print area - selection 4
Setting Print Range 2
PrintQuality setting - is -3 a valid setting? 3
Print sub returns 4
Apply Macro on Multiple Worksheets in a Workbook 1
Excel Page Setup on selected worksheets 2
Printing-don't need half this code but which half?:-) 5
Strange behavior with old macro now throws error with "Application 2

Top