quick way to copy excel formulas VBA

Question

I'm automating a HUGE sheet and I need to write a new macro for the workbook that will insert rows in a few sheets and then copy all the formulas. I have pastespecial = xlpasteformulas but that is causing the macro to run a little laggy, even with calculation set to automatic.

I know there is a way to have each cell something like this

this.formula = cellaboveit.formula

but that is copying the exact formula. I'm wondering if there's a way to offset the formulas in the cell above the cell.

Answer 1

Range("C4:D4").Resize(5).FillDown

will fill down formulas in C4:D4 to fill the next four rows

Answer 2

I think that setting calculation mode to manual before copying the formulas, and resetting it to automatic afterwards would be best. In automatic, it often wants to calculate the formula at each copy.

Application.Calculation = xlCalculationManual
'Do stuff here
Application.Calculation = xlCalculationAutomatic

In addition, I'd recommend if you are simply copying cell formulae down along a column, then not doing it cell by cell, but as a batch eg.

Range(myCopyCell).Select
Selection.Copy
Range(myPasteRange).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

where myCopyCell is the cell with the formula you want to copy, and myPastRange is the full range of where you want the formula to go.