CODEWITHSUNDEEP
vbaexcel
J.Smith
J.Smith

Reputation: 3

Copy/Insert Data from one Table to another

My objective is to copy data from table1 and insert it into table2. I tried the following :

        Sub Integration()
        Dim tbl1 As Range
        Dim tbl2 As Range
            Application.ScreenUpdating = False
                Set tbl1 = ActiveSheet.ListObjects("Table1").DataBodyRange
                Set tbl2 = ActiveSheet.ListObjects("Table2").DataBodyRange

                    tbl1.Copy
                    tbl2.Insert Shift:=xlDown

                Application.CutCopyMode = False
                tbl1.ClearContents
            Application.ScreenUpdating = True
        End Sub

But instead of copying Table1 and inserting it into Table2, it seems to copy Table2, ClearContent, and inserting it into itself with the data of Table 1.

The thing is that it runs perfectly on another sheet and it's the exact same code.
Any help would be appreciated. Thanks
Pre-Integration
After integration

Upvotes: 0

Views: 4767

Answers (1)

user6432984
user6432984

Reputation:

Use tbl2.Offset(tbl2.Rows.Count) to reference the first blank row after Table2. Next paste the contents of Table1 using tbl1.Copy tbl1.Copy tbl2.Offset(tbl2.Rows.Count).

Sub Integration()
    Dim tbl1 As Range
    Dim tbl2 As Range

    Application.ScreenUpdating = False
    Set tbl1 = ActiveSheet.ListObjects("Table1").DataBodyRange
    Set tbl2 = ActiveSheet.ListObjects("Table2").DataBodyRange

    tbl1.Copy tbl2.Offset(tbl2.Rows.Count)

    Application.CutCopyMode = False
    tbl1.ClearContents
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions