Row Insert with Marcro

[jmattingly85]

I have a Macro that I have recorded that is copying columns H and I, inserting two new columns and moving the previous H and I to J and K. In D3, I have the following formula: =SUMIF($H$2:$AAA$2,"Interest",$H$3:AAA3)

When I run the macro to insert the new columns, the absolute reference to H2 and H3 in the formula change to J2 and J3 - the don't retain their
absolute reference. Is there a way to make that stay at H or do I need to just edit the macro to update the formula?

Thanks!

 

[kweaver]

My first thought it not to put the formula directly in the sheet, but insert it via the macro after you do all the moving around.

 

[JoeMo]

This can be adapted to your code to fix the formula after the columns are inserted. The general idea is to store the formula in a variable, then put back into D3 after the columns are inserted.

Sub FixFormula()
Dim F As String
F = Range("D3").Formula
Columns("H:I").Insert
Range("D3").Formula = F
End Sub