0

How do i make this macro faster?

Windows("THe").Activate Sheets("The2").Select

Range("H8").Select
ActiveCell.FormulaR1C1 = _
    "=SUMIFS('Google'!C8,'Googe'!C1,RC21,'Google'!C5,RC1)"
Range("H8").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Community
  • 1
  • 1
user3147267
  • 39
  • 3

1 Answers1

3

1) Avoid using Select/Active statements

2) use With block

3) apply formula to entire range at once (instead copy/paste)

With Workbooks("THe").Sheets("The2")
    .Range("H8:H" & .Cells(.Rows.Count, "H").End(xlUp).Row).FormulaR1C1 = _
        "=SUMIFS('Google'!C8,'Googe'!C1,RC21,'Google'!C5,RC1)"
End With

and also don't use xlDown, use xlUp instead: How to determine last used row/column

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80