Excel Table: VBA to create a row in the first Table Row & populate the column sequence

[inddon]

Hello There,

I have an excel table and a button (Insert row) button linked to it.

When the User clicks on the button, a row should be created in the first row of the table. There is a sequence column 'Invoice Number'. As soon as the new row is the column should get a value (previous max row Invoice Number + 1).

Could you please advice how this can be done using VBA?

Attached is the sample workbook 1 for your reference.

Look forward to hearing from you.


Thanks in advance
Don

 

[Marc L]

Hi !

Just start with activating the Macro Recorder and operate manually :
you will get your own code base, a good way to learn !
Post here the generated code if you need any optimization …
​

 

[inddon]

Hi Marc,

Thank you for your reply.

I tried to record the macro as below:

Sub Macro1()

' Macro1 Macro
    Range("J21").Select
    Selection.ListObject.ListRows.Add (1)
End Sub

Thought it would generate the table name selection and do the insert row.

It should select the table name and the first row selection and insert row, but don't know how this can be done in code.


Regards,
Don

 

[Marc L]

Like you proceed from the first cell, the name is not necessary :​

Sub Macro1()
    [J21].ListObject.ListRows.Add 1
    [J21].Value = [J22].Value + 1
End Sub

 

[inddon]

Thank You Marc