I need a macro that copies formulae from multiple cells in a row to multiple rows and also updates the cell references.
For example, if cell A10 has formula =AA10+AB10, cell B10 has formula AB10+AC10, I need to copy these formula upwards to rows A2:B9 (each formula is to be copied upwards in the same column), so that formula in A9 is =AA9+AB9, in B9 is AB9+AC9, in A8 is AA8+AB8, in B8 is AB8+AC8 and so on as below:
A8=AA8+AB8; B8=AB8+AC8 (New copied formula)
A10=AA10+AB10; B10=AB10+AC10 (original formula)
The following macro copies formula in a range to a new range, but the starting reference is the same as in the original formula. For example, if the formula in cell A10 is AA10+AB10, the formula in Cell A2 will be AA10+AB10 (same as in cell A10) and is copied down so that formula in Cell A3 is AA11+AB11 instead of AA3+AB3
Sub formula () With Sheets ("Sheet1") .Range("A2:D9").formula = .Range("A10:D10").formula End With End Sub
Is it possible to modify it to update cell references after copying to new range