Add new Row to Table and insert data from range

Question

I have been asked to change data in a spreadsheet into a table.

Data needs to be pasted into this table from another workbook.

The code I have works fine whilst the data is in spreadsheet form but after being changed to a table I cannot get the code to add a new row or paste the data to that row.

This is the current code which works as long as the data is not in Table form.

Sub CopytoResults()

Dim destSht As Worksheet

Application.ScreenUpdating = False

'Copy the range 
ActiveSheet.Range("C52:AJ52").Select
Selection.Copy

'Open the Results spreadsheet and paste to the next available row
Workbooks.Open (ActiveWorkbook.Path & "\Quality_ResultsTST.xlsx")
Set destSht = ActiveWorkbook.Worksheets("Staff Results")

destSht.Activate

'Paste Data
destSht.Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues

'Save and Close the Results Spreadsheet
destSht.Parent.Close True

'Set Focus to the QA_QC Assessment form
Application.CutCopyMode = False
Windows("QA Form V2.xlsm").Activate
ActiveSheet.Range("E7").Select

Application.ScreenUpdating = True
    
End Sub

The Table name is Results

I've tried several solutions from this and other sites to no avail.

I know using select etc is bad form but this is not my code, I'm updating an old application.

Many Thanks

Answer 1

Using a Table it would be something like this:

Sub CopytoResults()
    
    Dim rngSrc as range, wb as workbook
    
    Application.ScreenUpdating = False
    
    'Copy the range 
    Set rngSrc = ActiveSheet.Range("C52:AJ52")
    
    set wb = Workbooks.Open(ActiveWorkbook.Path & "\Quality_ResultsTST.xlsx") 'ThisWorkbook?
    
    'add a new row to the table and assign the data to its Range
    with wb.sheets("Staff Results").listobjects("Results").listrows.add.range
        .value = rngSrc.value 
    end with

    wb.close true
    thisworkbook.Activate
    ActiveSheet.Range("E7").Select
    
End Sub