In VBA, can I add rows to a listobject in a sheet that contains multiple named tables?

Question

I am trying to create an interactive workbook that allows for project tracking in sheets but with a single input sheet. The current code takes project attributes from the input sheet and creates two Named Tables on a single new sheet in the workbook.

The first named table on the sheet is from "A2:P3" and this is the table I'd like to add rows to. The second named table on the sheet is from "A20:AB28" but it is hard coded to start at cell "A20" and I'd rather it didn't move as I add rows to the upper table.

The problem arises when I try to add a new project row to an already created sheet/table. I get error 1004 when I get to the line of code that is specifically suppose to add the new row. The code works when the second named table on the sheet isn't there but once I add in the second named table, I get the error.

This is in the Workbook Excel Object and it hasn't shown any issues moving between active sheets, the "i" variant denotes whichever sheet should be activated.

I've tried using ListRows.Add Position:=1, AlwaysInsert:=True but that doesn't work nor does removing the lrow variable middle-step.

Dim i As Variant, tbl As String, ProjectTable As ListObject, lrow As ListRow
 
' Define Working Sheet/Client
i = Worksheets("Create New Project").Range("New_Project_Client_Name").Value
tbl = (i & "Project")
Debug.Print (tbl)
    
'Set Project Table Name
Set ProjectTable = Worksheets(i).ListObjects(i & "Project")
    Debug.Print (Worksheets(i).Name)
    Debug.Print (ProjectTable.Name)

' Find last row in Named Project Table
Set lrow = ProjectTable.ListRows.Add 'Row where error occurs
 
    lrow.Range.Select
    lrow.Range(1).Value = Range("New_Project_Client_Name").Value
    lrow.Range(2).Value = Range("New_Project_Name").Value
    lrow.Range(3).Value = Range("New_Project_Budget").Value
    lrow.Range(4).Value = Range("New_Project_Status").Value
    lrow.Range(5).Value = Range("New_Project_Invoice_Type").Value
    lrow.Range(6).Value = Range("New_Project_Prepaid").Value

When I debug, the Project Table is referencing the correct worksheet and the correct table in the workbook. It just won't work when the second table (which is also named) is in the sheet.

Answer 1

Try this slightly different approach:

Dim ProjectTable As ListObject, lrow As ListRow

Set ProjectTable = ActiveSheet.ListObjects("Table5") 'for example...

With ProjectTable
    .Resize .Range.Resize(.Range.Rows.Count + 1)  'make the table one row larger
    Set lrow = .ListRows(.ListRows.Count)         'grab the (new) last row
End With