CODEWITHSUNDEEP
excelformula
james tanner
james tanner

Reputation: 53

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

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

Upvotes: 0

Views: 335

Answers (1)

ZAT
ZAT

Reputation: 1347

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.

Upvotes: 1

Related Questions