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

    Code
    1. Sub AddRow()
    2. With ActiveSheet.ListObjects("Table2").ListRows
    3. .Add AlwaysInsert:=True
    4. End With
    5. 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


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