Macro Slow Running for Page Setup Setting

[muhammad susanto]

hi all..
This lengthy statement adjusts the page setup properties on the active sheet, and is equivalent to this slower (but newer) method
cross post Faster Alternatives to VBA PageSetup - wellsr.com
here code :

Sub SlowPageSetup_Loop()
For Each sht In ActiveWorkbook.Sheets
    With sht.PageSetup
        .Zoom = False
        .Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.CentimetersToPoints(5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .FitToPagesWide = 2
        .FitToPagesTall = 1
        .CenterHorizontally = True
        .CenterVertically = False
    End With
Next sht
End Sub

how to make faster not slowly..
note : i use Ms Office 2010 till 2016

thank in advance

.sst

 

[davesexcel]

How many sheets are there?

 

[muhammad susanto]

How many sheets are there?

hi maximum 30 sheets

 

[davesexcel]

You should be able to group the sheets first

Sub GroupTabs()
    For i = Sheets(1).Index To Sheets(Sheets.Count).Index
        Sheets(i).Select Replace:=False
    Next i
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Zoom = False
        .Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.CentimetersToPoints(5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .FitToPagesWide = 2
        .FitToPagesTall = 1
        .CenterHorizontally = True
        .CenterVertically = False
    End With

End Sub

 

[muhammad susanto]

You should be able to group the sheets first

Sub GroupTabs()
    For i = Sheets(1).Index To Sheets(Sheets.Count).Index
        Sheets(i).Select Replace:=False
    Next i
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Zoom = False
        .Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.CentimetersToPoints(5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .FitToPagesWide = 2
        .FitToPagesTall = 1
        .CenterHorizontally = True
        .CenterVertically = False
    End With

End Sub

hi Dave, thanks a lot , once again how to your code can combined with code below, i want to single run macro.

Sub Insert_Current_Total_Page_Number_Footer()
'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
With ws.PageSetup
.CenterFooter = "&10&P of &N"
End With

End Sub

 

[davesexcel]

Is that for every sheet or for just sheet1?

 

[muhammad susanto]

hi.. for every sheet

 

[davesexcel]

I just randomly picked a line and placed your code in there.

 

[muhammad susanto]

I just randomly picked a line and placed your code in there.

View attachment 22943

hi Dave, thank you so much..!!