VBA to merge 2 tables across many sheets

[jessicabl]

Please see screenshots below. I received similar file every month with many tabs. In each tab, there are 2 tables (Table 1 and 2). I concatenate the ID (Column A) and Team Number (Column B) and use this concatenated identifier to merge the 2 tables. There are many columns in Table 1. In the screenshot, only a few columns are displayed.
Can you advise what is the VBA Code to merge these 2 tables into 1 table and output the data into a consolidated worksheet within the same file (see Table 3). I need to create a new column in column A and drop the tab name in, which is the name of the Salesperson. Preferably to sort the merged table by ID and Date. Repeat for the rest of the tabs.

 

[alansidman]

With Power Query

Book1
	A	B	C	D	E	F	G	H	I	J
1	ID	Team Number	Date	Sales Volume			ID	Team Number	Area	Unit Sold
2	12345	A1	5/1/2024	3 mil			12345	B2	East	2
3	12345	B2	2/2/2024	7 mil			12345	C3	North	1
4	12345	C3	7/1/2024	5 mil
5
6	ID	Team Number	Date	Sales Volume	Area	Unit Sold
7	12345	A1	5/1/2024	3 mil
8	12345	B2	2/2/2024	7 mil	East	2
9	12345	C3	7/1/2024	5 mil	North	1
Sheet1

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"Team Number"}, T2, {"Team Number"}, "T3", JoinKind.FullOuter),
    #"Expanded T3" = Table.ExpandTableColumn(MQ, "T3", {"Area", "Unit Sold"}, {"Area", "Unit Sold"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded T3",{{"Date", type date}})

in
    #"Changed Type"