Slow VBA macro?

[samilynn]

I've been using this Macro for quite a while, but lately it runs much muuuch muuuuuuch slower than it used to. What am I missing? Do you guys see anything that would cause this?
I am using Excel 2016
Thank you!!
Samantha

Sub PX_Macro()
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
With ws
With .PageSetup
.Orientation = xlLandscape
.PrintGridlines = True
.CenterFooter = "Page &P of &N"
.PrintTitleRows = "$1:$1"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.41)
.BottomMargin = Application.InchesToPoints(0.41)
.HeaderMargin = Application.InchesToPoints(0.15)
.FooterMargin = Application.InchesToPoints(0.15)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End With
Next
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
End With
Rows("1:1").EntireRow.AutoFit
Range("A2").Select
Columns("B:B").ColumnWidth = 15
Columns("C:C").ColumnWidth = 5.6
Columns("E:E").EntireColumn.AutoFit
Columns("F:I").Select
Selection.ColumnWidth = 8
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.ColumnWidth = 5.8
.Orientation = 0
End With
Range("F:F,G:G,H:H,I:I,K:K,M:M,N:N,O:O,P:P").Select
Selection.NumberFormat = "#,##0.00"
Columns("J:J").Select
Selection.NumberFormat = "##0.000"
Columns("L:L").Select
Selection.NumberFormat = "0.00%"
Range("K2").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=(RC[-1]-RC[-4])/RC[-4]"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[1]*1.78"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-4]*RC[2]/100"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-4]"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-1]*(RC[-7]/RC[-8])"
ActiveCell.Offset(1, -5).Range("A1").Select
Loop
Range("A2").Select
Range("F:F,G:G,H:H,I:I,K:K,M:M,N:N,O:O,P:P").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("R:R").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Range("H:H,L:L,O:O").Select
Selection.Font.Bold = True
Columns("Q:Q").ColumnWidth = 9.71
Cells.Select
Cells.EntireColumn.AutoFit
ActiveSheet.Sort.SortFields.Clear
Columns("A:AC").Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlYes
Range("A2").Select
ActiveWindow.FreezePanes = True
End Sub

 

[sax2play]

Samantha -

It looks like a macro that you recorded, which makes the macro very literal. Every time you select a cell, or scroll, the recorder will write that into the macro, making it very inefficient. Try to remove all of the ".Select" or "Selection." as they are not needed.

Original code (part of):

Rows("1:1").EntireRow.AutoFit
Range("A2").Select
Columns("B:B").ColumnWidth = 15
Columns("C:C").ColumnWidth = 5.6
Columns("E:E").EntireColumn.AutoFit
Columns("F:I").Select
Selection.ColumnWidth = 8
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.ColumnWidth = 5.8
.Orientation = 0
End With
Range("F:F,G:G,H:H,I:I,K:K,M:M,N:N,O:O,P:P").Select
Selection.NumberFormat = "#,##0.00"
Columns("J:J").Select
Selection.NumberFormat = "##0.000"
Columns("L:L").Select
Selection.NumberFormat = "0.00%"

try using this instead:

Rows("1:1").EntireRow.AutoFit
[S]Range("A2").Select[/S]  'delete this line as it is not needed
Columns("B:B").ColumnWidth = 15
Columns("C:C").ColumnWidth = 5.6
Columns("E:E").EntireColumn.AutoFit
Columns("F:I").ColumnWidth = 8
With Columns("D:D")
.HorizontalAlignment = xlCenter
.ColumnWidth = 5.8
.Orientation = 0
End With
Range("F:F,G:G,H:H,I:I,K:K,M:M,N:N,O:O,P:P").NumberFormat = "#,##0.00"
Columns("J:J").NumberFormat = "##0.000"
Columns("L:L").NumberFormat = "0.00%"

If you apply this to the rest of your macro, it should start to make it more efficient. Hope this helps!