Combine multiple tables of same format into one

[deepakgoyal2005]

I have an excel workbook with multiple sheets having data in Table format with exactly same columns. Need to combine data from all into 1 Table.

Sample:
Sheet "Sheet1" has a table under name "Table1" with 3 columns.

Sr. No.	Name	Login ID
1	Name 1	Login 1
2	Name 2	Login 2
3	Name 3	Login 3
4	Name 4	Login 4
5	Name 5	Login 5

Similarly, Sheet "Sheet2" has a table under name "Table2" with same 3 columns.

Sr. No.	Name	Login ID
6	Name 6	Login 6
7	Name 7	Login 7
8	Name 8	Login 8
9	Name 9	Login 9
10	Name 10	Login 10
11	Name 11	Login 11

And, likewise there could be more sheets added/removed in future.

There is another Sheet "SheetC" with similar table "TableC" where a combined output with data from other table needs to be created.

Trying to create a macro to loop through all the tables except the output combined table "TableC" and append the records in output "TableC". But I am facing trouble creating a loop. Tried googling through available samples, but they were not using data in table format, and hence were of little help.

Would really appreciate, if someone could help with their expertise. Thanks in advance.

VBA macro (WIP):

Sub Combine_Tables()
'
' Consolidate data from Multiple tables
'
    Dim NextRow As Long  'To identify the last blank row in combined table
    
' Clear content in output combined table
    ActiveWorkbook.Sheets("Combine").Range("TableC").ClearContents
    
'Copy content from first "Table1"
    ActiveWorkbook.Sheets("Sheet 1").Select
    Range("Table1").Select
    Selection.Copy
    
    ActiveWorkbook.Sheets("Combine").Select
    Range("B3").Select
    Selection.End(xlDown).Select
    Range("B8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

 

[Automatrix]

Power Query solution: Make a connection (query) to each of the tables. Append queries.

Book1
	A	B	C	D	E	F	G
1	Sr. No.	Name	Login ID		Sr. No.	Name	Login ID
2	1	Name 1	Login 1		1	Name 1	Login 1
3	2	Name 2	Login 2		2	Name 2	Login 2
4	3	Name 3	Login 3		3	Name 3	Login 3
5	4	Name 4	Login 4		4	Name 4	Login 4
6	5	Name 5	Login 5		5	Name 5	Login 5
7					6	Name 6	Login 6
8	Sr. No.	Name	Login ID		7	Name 7	Login 7
9	6	Name 6	Login 6		8	Name 8	Login 8
10	7	Name 7	Login 7		9	Name 9	Login 9
11	8	Name 8	Login 8		10	Name 10	Login 10
12	9	Name 9	Login 9		11	Name 11	Login 11
13	10	Name 10	Login 10
14	11	Name 11	Login 11
Sheet1

 

[Fluff]

Power Query solution: Make a connection (query) to each of the tables. Append queries.

Maybe you could show how, or even supply the M-code, as other members do.

 

[Automatrix]

Sure. First I connected to one table (I started with Table2), and loaded it as a connection only (just to keep things tidy). It did the "Changed type" step automatically, and I was happy with the types it chose.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sr. No.", Int64.Type}, {"Name", type text}, {"Login ID", type text}})
in
    #"Changed Type"

Then I loaded Table1, and picked the Append Queries action. This table I loaded as a new table in cell E1.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sr. No.", Int64.Type}, {"Name", type text}, {"Login ID", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Table2})
in
    #"Appended Query"

 

[deepakgoyal2005]

I tried Power Query as an option, but there were 2 problems,

1) When new tables are added to the sheet, the query needs to be recreated or modified. In VBA, this cab be taken care by a loop eg. "IF "table name" <> "TableC", pick the data
2) Output is created in a new table, and cannot be created in an existing table, like "TableC" here in my example

 

[Automatrix]

I tried Power Query as an option, but there were 2 problems,

1) When new tables are added to the sheet, the query needs to be recreated or modified. In VBA, this cab be taken care by a loop eg. "IF "table name" <> "TableC", pick the data
2) Output is created in a new table, and cannot be created in an existing table, like "TableC" here in my example

1) That can be solved with loading from a Folder instead of from specified, hard-coded tables. Here is a guide:

2) That is correct. Why do you need to load the data to an existing table?

In general, you can do things in VBA that are not (currently) possible in Power Query. But Power Query is much easier to learn.

 

[deepakgoyal2005]

Thanks Automatrix, for spending time over my case. Appreciate.

Need the output in a specific table because I have a dashboard created over the output table, else it would be manual to transfer data from 1 to another.

You are right that power query is easier to learn, but doesn't seem to fit my requirement. I am trying to figure a solution in VBA and working on the code. I am hopeful on getting some help from the experts in this forum, like everytime.

 

[Automatrix]

Thanks Automatrix, for spending time over my case. Appreciate.

Need the output in a specific table because I have a dashboard created over the output table, else it would be manual to transfer data from 1 to another.

You are right that power query is easier to learn, but doesn't seem to fit my requirement. I am trying to figure a solution in VBA and working on the code. I am hopeful on getting some help from the experts in this forum, like everytime.

Would it be possible to have the dashboard be based on the table generated by Power Query instead? You can also build the dashboard in Power BI, which is literally created to handle that sort of stuff.

 

[deepakgoyal2005]

For the dashboard, data references has to be fixed.
We are not using PowerBI for now.