Targeting a Table with name taken from cell value

Question

I am trying to add to specific Tables, based on the Table name in cell A2. I'm unsure if I am searching with the right terminology but this is what I have no far, I obviously get a mismatch error:

Sub addToExistingTable()

    Dim tblname As ListObject
    Dim nNewRow As ListRow

    tblname = Range("A2").Value
    Sheets("sheet1").ListObjects(tblname).Range.Select
    Set nNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    nNewRow.Range.Cells(1, 1).Value = Range("A1").Value

End Sub  

Has anyone else had this problem and how did you get around it?

Answer 1

There is a decided ambiguity between the worksheet that Range("A2") is on and the worksheet where the table resides. However, reading further into the code seems to indicate that once you have selected the table you want to add the value from Range("A1") (again with no parent worksheet specified so must be on the same worksheet as the table just selected) into a new row on the table so Range("A1:A2") are almost assuredly on Sheet1 (the same worksheet as the table).

Sub addToExistingTable()

    Dim tblname As String
    Dim nNewRow As ListRow

    With Worksheets("Sheet1")
        tblname = .Range("A2").Value

        With .ListObjects(tblname)
            Set nNewRow = .ListRows.Add(AlwaysInsert:=True)
            nNewRow.Range.Cells(1, 1).Value = .Range("A1").Value
        End With
    End With
End Sub

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.