Insert one table into another table, when column header names are not in the same order

Question

I want to insert all the data from one table copy_tbl at the end of another table paste_tbl both in different worksheets, but in the same workbook.

The columns that have the same header name will not be in the same order in both tables, so I need to rearrange the order of the inserted data columns to match the destination.

The copy_tbl might have column header names that doesn't exist in paste_tbl, and I want those columns to be added to paste_tbl (this is optional and the columns that doesn't have a match in the destination table can be ignored, if it's a headache to do).

I've been trying to read up on ListObjects and also trying to code some kind of loop that matches column header names before inserting data, but I'm stuck.

This seems like a fairly simple task, and I was certain I would at least figure it out with the help of google, but I've been sitting here for 6 hours now and I'm not getting anywhere.

I did find a solution to this by using Power Query, but I'm on a Mac, and it hasn't been implemented in Excel for Mac yet :|

Is there someone willing to help me solve this problem?

Thanks in advance!

Edit:

I abandoned trying to use ListObjects and tried something else. This is the code I've come up with so far:

    Dim r As Range, c As Range, msg As String
    
    Application.ScreenUpdating = False
        
    With Sheets("copy_sht").Range("1:1").CurrentRegion
        For Each r In Sheets("paste_sht").Range("1:1")
            Set c = .Rows(1).Find(r.Value, , , xlWhole, , 0)
            If Not c Is Nothing Then
                .Columns(c.Column).copy
                r.PasteSpecial xlPasteValues
            Else
                msg = msg & vbLf & r.Value
            End If
        Next
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
                
    End With

End Sub

This replaces some of the values into the correct cells, and it also overwrites the whole first column with data from the second table.

This is what I want to happen:

╔════════════╦═══════╦═══════╦═══════╦═══════╗
║     Number ║ info1 ║ info2 ║ info3 ║ info4 ║
╠════════════╬═══════╬═══════╬═══════╬═══════╣
║          1 ║ abc   ║       ║ xyz   ║   456 ║
║          2 ║       ║ 123   ║       ║       ║
║          3 ║       ║       ║ 456   ║       ║
║          4 ║       ║       ║       ║       ║
║          5 ║ abc   ║ asd   ║ zxc   ║       ║
╚════════════╩═══════╩═══════╩═══════╩═══════╝
╔════════════╦═══════╦═══════╦═══════╦═══════╗
║     Number ║ info1 ║ info3 ║ info4 ║ info5 ║
╠════════════╬═══════╬═══════╬═══════╬═══════╣
║          1 ║ def   ║ www   ║   123 ║ a     ║
║          3 ║       ║       ║       ║ b     ║
║          5 ║       ║       ║       ║ c     ║
║          6 ║       ║       ║       ║ d     ║
║          7 ║ 123   ║       ║       ║ e     ║
╚════════════╩═══════╩═══════╩═══════╩═══════╝
╔════════════╦════════╦═══════╦════════╦════════╦═══════╗
║     Number ║ info1  ║ info2 ║ info3  ║ info4  ║ info5 ║
╠════════════╬════════╬═══════╬════════╬════════╬═══════╣
║          1 ║ abcdef ║       ║ xyzwww ║ 456123 ║ a     ║
║          2 ║        ║ 123   ║        ║        ║       ║
║          3 ║        ║       ║  456   ║        ║ b     ║
║          4 ║        ║       ║        ║        ║       ║
║          5 ║ abc    ║ asd   ║ zxc    ║        ║ c     ║
║          6 ║        ║       ║        ║        ║ d     ║
║          7 ║ 123    ║       ║        ║        ║ e     ║
╚════════════╩════════╩═══════╩════════╩════════╩═══════╝

The first table paste_tbl will become the third table after the data has been added from the second table copy_tbl.

This is what the code actually does with the first table:

╔═════════╦═══════╦═══════╦═══════╦═══════╗
║  Number ║ info1 ║ info2 ║ info3 ║ info4 ║
╠═════════╬═══════╬═══════╬═══════╬═══════╣
║       1 ║ def   ║       ║ www   ║   123 ║
║       3 ║       ║ 123   ║       ║       ║
║       5 ║       ║       ║       ║       ║
║       6 ║       ║       ║       ║       ║
║       7 ║ 123   ║ asd   ║       ║       ║
╚═════════╩═══════╩═══════╩═══════╩═══════╝

So what I'm still missing is:

• Adding missing columns from copy_tbl to paste_tbl.
• Making sure the data that is pasted from copy_tbl is matched with the same unique ID located in the first column in paste_tbl, and if that unique ID doesn't exist, instead paste the whole row at the bottom of the table.
• If data already exists in a destination cell, I want to concatenate that with the data from copy_tbl.

---

Edit 2: Realizing some parts of VBA I've never understand, I tried to do this the old fashioned way with formulas, and I succeeded.

It's probably not very optimized, because it takes a while to populate the table, and I was getting tired when I finished it. But it gets it done.

Sorry if it's a bit messy, but what it does is it populates a new table basen on the values of two other tables tabell1and tabell2. It merges any existing cells data with the same unique ID $A2 which needs to be in the first column of all tables. You have to collect all the unique ID's and place them without duplicates in the table that is to be populated. Then just paste the following formula into the first row of every column of your table.

=IFERROR(
IF(
      IF(
            COUNTIF(tabell1;$A2)=1;
                   VLOOKUP($A2;tabell1;MATCH(B$1;tabell1[#Headers];0);FALSE);""
            )
                     =
        IF(
                             COUNTIF(tabell2;$A2)=1;
                             VLOOKUP($A2;tabell2;MATCH(B$1;tabell2[#Headers];0);FALSE);""
            );
                   IF(
                           AND(COUNTIF(tabell1;$A2)=1;COUNTIF(tabell2;$A2)=1);
                           IFERROR(
                                 VLOOKUP($A2;tabell1;MATCH(B$1;tabell1[#Headers];0);FALSE);
                                  VLOOKUP($A2;tabell2;MATCH(B$1;tabell2[#Headers];0);FALSE));
CONCATENATE(VLOOKUP($A2;tabell1;MATCH(B$1;tabell1[#Headers];0);FALSE);" ";VLOOKUP($A2;tabell2;MATCH(B$1;tabell2[#Headers];0);FALSE)));
IF(
                             COUNTIF(tabell2;$A2)=1;
                             VLOOKUP($A2;tabell2;MATCH(B$1;tabell2[#Headers];0);FALSE);VLOOKUP($A2;tabell1;MATCH(B$1;tabell1[#Headers];0);FALSE)
            ));" ")