Excel: copy entire row and contents but change cell reference?

Question

I am using the following code to copy a row in excel and all of its contents when a user clicks on a certain cell in column A.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayAlerts = False
If Target.Row > 150 And Target.Column = Range("A1").Column Then
  On Error Resume Next
    With ActiveCell
        .EntireRow.Copy
        .EntireRow.Insert
    End With
    Application.CutCopyMode = False
 End If

this copies the row above and all of its contents, however In the cell in column B, I have a formula like so

='[NewSupplierSet-Up.xls]New Supplier Set-Up'!$B17

what I want is for the cell reference in this formula to increment by 1 each time the row is copied? so that the next row will be

='[NewSupplierSet-Up.xls]New Supplier Set-Up'!$B18

can someone please show me if there is a way of doing this? Thanks

ZAT ZAT · Accepted Answer · 2014-09-24T14:13:15

Copied row may be inserted below the active cell.

With ActiveCell
    .EntireRow.Copy
    .EntireRow.Offset(1, 0).Insert
End with

Then the cell references inside formulas would increment.

Excel: copy entire row and contents but change cell reference?

1 Answers