This can be reduced:
Range("W1").EntireColumn.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("X1").EntireColumn.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("Y1").EntireColumn.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("Z1").EntireColumn.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
..to
Sheets(MyArr(Itm)).Range("W1:Z1").EntireColumn.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Try adding this and moving this down:
LR = Sheets(MyArr(Itm)).Range("A" & Rows.Count).End(xlUp).Row
With Sheets(MyArr(Itm)).Range("W2:Z" & LR)
.Value = Evaluate("IF(ROW(1:" & .Cells.Count & ")," & .Address & " / 10)")
End With
Sheets(MyArr(Itm)).Range("W" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=SUBTOTAL(9, R1C:R[-1]C)"
Sheets(MyArr(Itm)).Range("Z" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=SUBTOTAL(9, R1C:R[-1]C)"
Sheets(MyArr(Itm) & "").Columns.AutoFit
Sheets(MyArr(Itm) & "").Name = Left(Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Proper(Trim(Sheets(MyArr(Itm) & "").Range("AE2").Text)), "[", ""), "]", ""), "?", ""), "\", "-"), "/", "-"), 31)
Next Itm
Bookmarks