Hi all -

My print macro below seems very slow
What can I do to make it faster?

Subprocedures used
ShowShop1 'Hides all col's except the shoppe in question
BeforePrint 'Header row color index to black, font to white/bold
AfterPrint 'Header row returned to on screen viewing format


Thanks
-goss

Print Code:
Sub PrintProcess()

Dim wb As Workbook
Dim wsMarket As Worksheet
Dim rngPrint As Range
Dim lngRows As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

Set wb = ThisWorkbook
Set wsMarket = wb.Worksheets("Market")
lngRows = wsMarket.Range("A65536").End(xlUp).Row
Set rngPrint = wsMarket.Range("E2:N" & lngRows)

ShowShop1
BeforePrint


'================================================= ========================
'/Print Process
With Sheets("Market")
.PageSetup.PrintArea = rngPrint.Address
' .PageSetup.PrintArea = .Range("rngPrint").Address
End With

With wsMarket.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""MyCo Market Price
Analysis"
.RightHeader = ""
.LeftFooter = "&8&D"
.CenterFooter = ""
.RightFooter = "&8&T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'/End Print Process

'================================================= ========================

AfterPrint
wsMarket.Range("A1").Select

Set wb = Nothing
Set wsMarket = Nothing
Set rngPrint = Nothing

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

End Sub

get rid of the lines you don't reall need. each one is a call to the printer
driver!
Perhaps:
With wsMarket.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.CenterHeader = "&""Arial,Bold""MyCo Market Price
Analysis"
.LeftFooter = "&8&D"
.RightFooter = "&8&T"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With


" wrote:

Hi all -

My print macro below seems very slow
What can I do to make it faster?

Subprocedures used
ShowShop1 'Hides all col's except the shoppe in question
BeforePrint 'Header row color index to black, font to white/bold
AfterPrint 'Header row returned to on screen viewing format


Thanks
-goss

Print Code:
Sub PrintProcess()

Dim wb As Workbook
Dim wsMarket As Worksheet
Dim rngPrint As Range
Dim lngRows As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

Set wb = ThisWorkbook
Set wsMarket = wb.Worksheets("Market")
lngRows = wsMarket.Range("A65536").End(xlUp).Row
Set rngPrint = wsMarket.Range("E2:N" & lngRows)

ShowShop1
BeforePrint


'================================================= ========================
'/Print Process
With Sheets("Market")
.PageSetup.PrintArea = rngPrint.Address
' .PageSetup.PrintArea = .Range("rngPrint").Address
End With

With wsMarket.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""MyCo Market Price
Analysis"
.RightHeader = ""
.LeftFooter = "&8&D"
.CenterFooter = ""
.RightFooter = "&8&T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'/End Print Process

'================================================= ========================

AfterPrint
wsMarket.Range("A1").Select

Set wb = Nothing
Set wsMarket = Nothing
Set rngPrint = Nothing

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

End Sub

On Apr 10, 9:30 am, Bob Umlas, Excel MVP
wrote:
get rid of the lines you don't reall need. each one is a call to the printer
driver!
Perhaps:
With wsMarket.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.CenterHeader = "&""Arial,Bold""MyCo Market Price
Analysis"
.LeftFooter = "&8&D"
.RightFooter = "&8&T"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With



" wrote:
Hi all -

My print macro below seems very slow
What can I do to make it faster?

Subprocedures used
ShowShop1 'Hides all col's except the shoppe in question
BeforePrint 'Header row color index to black, font to white/bold
AfterPrint 'Header row returned to on screen viewing format

Thanks
-goss

Print Code:
Sub PrintProcess()

Dim wb As Workbook
Dim wsMarket As Worksheet
Dim rngPrint As Range
Dim lngRows As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

Set wb = ThisWorkbook
Set wsMarket = wb.Worksheets("Market")
lngRows = wsMarket.Range("A65536").End(xlUp).Row
Set rngPrint = wsMarket.Range("E2:N" & lngRows)

ShowShop1
BeforePrint

'================================================= ========================
'/Print Process
With Sheets("Market")
.PageSetup.PrintArea = rngPrint.Address
' .PageSetup.PrintArea = .Range("rngPrint").Address
End With

With wsMarket.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""MyCo Market Price
Analysis"
.RightHeader = ""
.LeftFooter = "&8&D"
.CenterFooter = ""
.RightFooter = "&8&T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'/End Print Process

'================================================= ========================

AfterPrint
wsMarket.Range("A1").Select

Set wb = Nothing
Set wsMarket = Nothing
Set rngPrint = Nothing

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

End Sub- Hide quoted text -

- Show quoted text -

Thanks Bob -

Begs the question, I have all of that stuff configured manually
through page setup
Why do I need it in the vba code?
The only thing that will change with each report run is the print
range

Thanks
-goss

You don't need it. Just set the printarea.

--
Regards,
Tom Ogilvy


" wrote:

On Apr 10, 9:30 am, Bob Umlas, Excel MVP
wrote:
get rid of the lines you don't reall need. each one is a call to the printer
driver!
Perhaps:
With wsMarket.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.CenterHeader = "&""Arial,Bold""MyCo Market Price
Analysis"
.LeftFooter = "&8&D"
.RightFooter = "&8&T"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With



" wrote:
Hi all -

My print macro below seems very slow
What can I do to make it faster?

Subprocedures used
ShowShop1 'Hides all col's except the shoppe in question
BeforePrint 'Header row color index to black, font to white/bold
AfterPrint 'Header row returned to on screen viewing format

Thanks
-goss

Print Code:
Sub PrintProcess()

Dim wb As Workbook
Dim wsMarket As Worksheet
Dim rngPrint As Range
Dim lngRows As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

Set wb = ThisWorkbook
Set wsMarket = wb.Worksheets("Market")
lngRows = wsMarket.Range("A65536").End(xlUp).Row
Set rngPrint = wsMarket.Range("E2:N" & lngRows)

ShowShop1
BeforePrint

'================================================= ========================
'/Print Process
With Sheets("Market")
.PageSetup.PrintArea = rngPrint.Address
' .PageSetup.PrintArea = .Range("rngPrint").Address
End With

With wsMarket.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""MyCo Market Price
Analysis"
.RightHeader = ""
.LeftFooter = "&8&D"
.CenterFooter = ""
.RightFooter = "&8&T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'/End Print Process

'================================================= ========================

AfterPrint
wsMarket.Range("A1").Select

Set wb = Nothing
Set wsMarket = Nothing
Set rngPrint = Nothing

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

End Sub- Hide quoted text -

- Show quoted text -

Thanks Bob -

Begs the question, I have all of that stuff configured manually
through page setup
Why do I need it in the vba code?
The only thing that will change with each report run is the print
range

Thanks
-goss