Adding a new row with data to excel sheet using VBA

Question

I am adding a new row to a table but want to then add the data to that row that I just added. I am thinking something like this, but not sure how to add each columns data to that new row. My table has 4 columns named "store" "emp#" "date" & "amt". I have specific data that I will put in each column. I simplified the code, as there is a whole lot more to the macro, but just stuck on this part. Thank you for you help.

Dim rt_ws As Worksheet
Dim rt_tbl As ListObject

Set rt_ws = ThisWorkbook.Worksheets("RT Clock Hours")
Set rt_tbl = rt_ws.ListObjects("rt_hours")

With rt_table.ListRows.Add

    .  `this is where I am not sure what to do`
    .
    .

End Sub

YasserKhalil · Accepted Answer · 2020-05-04 08:39:24Z

1

Try this code

Sub Test()
    Dim ws As Worksheet, tbl As ListObject

    Set ws = ThisWorkbook.Worksheets("RT Clock Hours")
    Set tbl = ws.ListObjects("rt_hours")

    With tbl.ListRows.Add
        .Range = Array("Store1", "1530", "05/03/2020", "Amt1")
    End With
End Sub

edited May 4, 2020 at 8:39

answered May 3, 2020 at 0:23

YasserKhalil

9,60813 gold badges62 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ron Rosenfeld Over a year ago

I think .Resize is redundant. Shouldn't the .Range property of the ListRow object already be the same column size as the Table?

Collectives™ on Stack Overflow

Adding a new row with data to excel sheet using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related