none
update from one table to another table using ListObject RRS feed

  • Question

  • Hi,

    I have used the example from this well presented post: 

    Mike MLH

    In practice you wouldn't select anything you should use .copy instead

    so this will paste the data body of table 2,3 & 4 onto the end of Table1

    Sub SomeSub()
    Dim LastRow As Variant, x As Long
    For x = 2 To 4
    Set LastRow = ActiveSheet.ListObjects("Table1").ListRows.Add
    ActiveSheet.ListObjects("Table" & x).DataBodyRange.Copy
    LastRow.Range.PasteSpecial xlPasteValues
    Next
    End Sub

    Being new to VBA my attempt at applying this to making a refresh of repeated activities in my activity tracker comes up with errors that I cant fathom.

    Both tables have the same columns and formula.

    my current code looks like this:

    Sub UPDATEpa()
    
    'dimensioning    Source table (SourceTbl)
    '                Target table (TargetTbl)
    '                Target table add row to last row (TargetTblLastRow)
    
    Dim SourceTbl As ListObject
    Dim TargetTbl As ListObject
    Dim TargetTblLastRow As Variant
    
    On Error GoTo ErrHandler
    
    Set SourceTbl = ActiveSheet.ListObjects("RepeatActivities")
    Set TargetTbl = ActiveSheet.ListObjects("Activity")
    Set TargetTblLastRow = Ttbl.ListRows.Add
    
        SourceTbl.DataBodyRange.Copy
        TargetTblLastRow.Range.PasteSpecial xlPasteValues
        
    Exit Sub
    ErrHandler:
        MsgBox "An error has occured at line " & i - 1 & " or" & i, , "Error Macro"
    End Sub
    
    All ideas are welcome. If you have a more efficient solution I'm keen to see it.

    Friday, July 20, 2018 8:32 AM

All replies

  • Hello John stats,

    It seems that you want to copy data in table RepeatActivities to table Activity, right?

    If so, you could only below code to do this work. If not, please detail us what's your need and where did you get which error during testing.

    Set TargetTblLastRow = Ttbl.ListRows.Add
    =>
    Set TargetTblLastRow = TargetTbl.ListRows.Add

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 23, 2018 6:03 AM