VBA Copy formula without changing reference

[csb31]

Hello I have a simple macro to copy a row and insert below. Works like a charm. However...

Sub AddRow()
'
'
'
    ActiveCell.EntireRow.Copy
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown
 
End Sub

This alters the formulas relative references. This is what I want EXCEPT for the formula in the A column cell. When I insert this new row I want the formula in the A cell from the original row to copy EXACTLY, not to shift, to the A cell in the new row. If the formula is =B3 in the original I want it to be =B3 in the new row, NOT =B4 etc. The formula in the A column is not consistent across the sheet so it has to pull it from the cell, the macro can't provide it. For reasons related to other macros and functions I can't just use $ absolute references. Any advice. I tried to record a few macros to get an idea but it hasn't worked. Thanks in advance!

 

[Andrew Poulsom]

Try:

Sub AddRow()
    With ActiveCell
        .EntireRow.Copy
        .Offset(1, 0).EntireRow.Insert Shift:=xlDown
        .EntireRow.Cells(2, 1).Formula = .EntireRow.Cells(1, 1).Formula
    End With
End Sub

 

[shg]

One way:

Sub AddRow()
    With Cells(ActiveCell.Row, "A")
        .EntireRow.Copy
        .Offset(1).Insert
        .Offset(1).Formula = .Formula
    End With
    Application.CutCopyMode = False
End Sub

 

[csb31]

Success. They both worked. Thanks a ton, I knew it couldn't be super difficult. Thanks for the help!