For each new row inserted, add value + 1 to column

Question

I have code that inserts a new row every time I press the command button. However, I would like to alter the code so that every time I press the command button, a value, starting with "1" is inserted into Column D (in the row where the new row is created). When the button is pressed again, and a new row is inserted again, "2" would appear in D10 for instance if the row prior was inserted in row 9.

Currently I have code that just adds the new row, plus pastes some formatting/values that I need.

`Private Sub CommandButton2_Click()

Dim lRow As Long
Dim lRsp As Long
On Error Resume Next

lRow = Selection.Row()
lRsp = MsgBox("Insert New row above " & lRow & "?", _
        vbQuestion + vbYesNo)
If lRsp <> vbYes Then Exit Sub

Rows(lRow).Select
Selection.Copy
Rows(lRow + 1).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

Paste formulas and conditional formatting in new row created
Rows(lRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
i = 1
Range("D9").Value = i


End Sub`

I recommend you remove `On Error Resume Next`. It is hiding the fact that you are trying to `PasteSpecial` after you have exited `CutCopyMode`. — Darrell H, Jul 11 '19 at 18:04

score 0 · Answer 1 · answered Jul 11 '19 at 18:01

Try:

Private Sub CommandButton2_Click()

    Dim lRow As Long
    Dim lRsp As Long
    On Error Resume Next

    lRow = Selection.Row()
    lRsp = MsgBox("Insert New row above " & lRow & "?", _
        vbQuestion + vbYesNo)
    If lRsp <> vbYes Then Exit Sub

    Rows(lRow).Copy 'REMOVED SELECT
    Rows(lRow + 1).Insert Shift:=xlDown 'REMOVED SELECT
    Application.CutCopyMode = False

    'Paste formulas and conditional formatting in new row created
    Rows(lRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
    'i = 1 'REMOVED
    'Range("D9").Value = i 'REMOVED
    cells(lrow,4).value = Application.max(columns(4))+1 'ADDED

End Sub

Also, try and go back through this and remove (where applicable) Select and Selection... see How to avoid using Select in Excel VBA

For each new row inserted, add value + 1 to column

1 Answers1