Copy and paste the complete row from one table to another table

Question

I am trying to select the complete row in table1 and paste into table5.

Selected successfully but copy is missing.

Sub Test()
    Dim xRow As Long
    Dim tbl As ListObject
    Dim I As Long
    Dim J As Long

    For I = 1 To ActiveSheet.ListObjects("Table2").ListColumns(1).Range.Count - 1
        MsgBox (ActiveSheet.ListObjects("Table2").DataBodyRange.Cells(I, 1))
        For J = 1 To ActiveSheet.ListObjects("Table3").ListColumns(1).Range.Count - 1
            If ActiveSheet.ListObjects("Table2").DataBodyRange.Cells(I, 1) = ActiveSheet.ListObjects("Table3").DataBodyRange.Cells(J, 1) Then
                ActiveSheet.ListObjects("Table3").ListRows(J).Range.Select
                ActiveSheet.ListObjects("Table3").ListRows(J).Range.Copy
                Worksheets("RECHNUNGS 1").Active
                ActiveSheet.ListObjects("Table13").ListRows(J).Range.Select
                ActiveSheet.Paste
            End If
        Next J
    Next I
End Sub

1

So what is happening exactly? – QHarr Sep 25 '18 at 11:00

score 1 · Answer 1 · answered Sep 25 '18 at 11:07

Try to isolate the not working code and make it work. Imagine you want to copy row 3 to row 10 from the left to the right table:

This is the minimal working code, that would do it:

Option Explicit

Sub TestMe()

    With ActiveSheet
        .ListObjects("Table3").ListRows(3).Range.Copy
        .ListObjects("Table13").ListRows(10).Range.PasteSpecial xlPasteAll
    End With
    Application.CutCopyMode = False

End Sub

And then try to build upon it. As you see, it is using .PasteSpecial xlPasteAll and not paste, because .Paste is a method of the Worksheet.

Consider this - How to avoid using Select in Excel VBA

Copy and paste the complete row from one table to another table

1 Answers1