Insert New Row to Table and Increment Number

  • I have a table that tracks invoices.

    I need assistance in

    (1) when I click my "add row" button, column A's invoice number increases by 1 from the row above, but I want the value to start at 57 or whatever number is prior, not be stuck at 1.

    (2) Also need it so it is editable/flexible in changing number. If I want to type in a different value for column A, it will remain, without it defaulting back to the sequence.

    (3) And need help in making column B's value the current date. Currently I just have a formula =today() on the table that has been autofilled down, and its been working, but if possible want it coded into VBA

    Thank you!

    'this is the macro I have for add a new row

    Sub AddRow()
    With ActiveSheet.ListObjects("Table2").ListRows
     .Add AlwaysInsert:=True
    End With
    End Sub

    'this is the macro I saw on this site, and it works but not all the time, and starts value from 1.

    Sub hello() Dim wsS1 As Worksheet Dim lRows As Long Dim Iint As Integer Set wsS1 = Worksheets("Summary Revenue 2020") lRows = wsS1.Range("A65536").End(xlUp).Row For Iint = 5 To lRows wsS1.Range("A" & Iint + 1).Value = wsS1.Range("A" & Iint).Value + 1 Next Iint End Sub

  • Try this

    Sub AddRow()
    Dim oNewRow As ListRow
    With ActiveSheet.ListObjects("Table2")
    Set oNewRow = .ListRows.Add(AlwaysInsert:=True)
    oNewRow.Range.Cells(1, 1).Value = Application.WorksheetFunction.Max(.ListColumns(1).DataBodyRange) + 1
    End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!