VBA set printing area for all sheets in a workbook - takes too long to run

[lynnsong986]

Hello,

I have the below codes that run really slowly, it loops each sheet and find the dynamic range then set the printing area on each. it works but takes a long time (the workbook has about 130 sheets and it takes almost 30 minutes to loop through!)

Can someone please help me revise it to make it run faster?

Also how can I post codes where it belongs...


Sub SetPrintAreaOnMultipleSheetsForReport()
Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.PrintCommunication = False
sh.Activate
myrange = ActiveSheet.Cells(Rows.Count, 9).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange
ActiveSheet.PageSetup.Orientation = xlPortrait
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 20
End With
Next sh
Application.PrintCommunication = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

thanks,
Lynn

 

[MARK858]

Any faster?...

Sub SetPrintAreaOnMultipleSheetsForReport()
    Dim sh As Worksheet
    For Each sh In ActiveWindow.SelectedSheets
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.PrintCommunication = False

        myrange = sh.Cells(Rows.Count, 9).End(xlUp).Address
        sh.PageSetup.PrintArea = "$A$1:" & myrange
        sh.PageSetup.Orientation = xlPortrait
        With sh.PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = 20
        End With
    Next sh
    Application.PrintCommunication = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[lynnsong986]

Thanks very much Mark858, the revised codes run faster - I get it because it doesn't need to activate each sheet any more.

also thanks for showing me the code tags...

Lynn

 

[MARK858]

You're welcome, btw the easiest way these days to add the code tags is to copy your code into the thread, select the code and click the # icon above the window.

 

[lynnsong986]

Thanks so much for letting me know!
Lynn