Add value in a table even if the table changes location in the sheet

Question

I have a table below another table.
If I add a lot of values in the first table, the second table will change cells and go further below.

Sub Macro4()
    Range("Table2[Work]").Select
    Selection.ListObject.ListRows.Add (1)
    Range("A24").Value = Now
    Range("B24").Value = VBA.Environ("Username")
End Sub

I'm using range (A24 and B24) to add values. I would rather have them follow up the table whatever it's location instead of hardcoding it.

Answer 1

Write to the Last Row of a Table

• If you're trying to write to the last row of a table, try the following. Adjust the worksheet name though.

The Code

Option Explicit

Sub NewTableEntry()
    
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1") ' The worksheet containing the table.
    
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Table2")
    
    ' If you fully understand the previous, then rather use the following:
    'Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table2")
    
    ' Add a new row to the bottom of the table.
    tbl.ListRows.Add
    ' Write values to the last row in the table.
    With tbl.DataBodyRange.Rows(tbl.DataBodyRange.Rows.Count)
        .Cells(1).Value = Now
        .Cells(2).Value = VBA.Environ("Username")
    End With

End Sub