Combining Tables

[happyhungarian]

Hi, I have tables on multiple worksheets that all have the same column headings for each and every column of the table. I was hoping to create a consolidated table on a summary sheet. Is there an easy way to do this automatically without manual copy/paste? Would also like to have the consolidated table update immediately if one of the source tables has data added to it. A normal table or a pivot table would work!

Thank you as always!

Jesse


EDIT: Pivot table would actually be preferred

 

[Misca]

Sounds like a job for the Power Query a.k.a. Get & Transform: Do it once and reap the rewards ever since.

I mean try it (You'll find Get And Transform from the Data tab of your ribbon) and you'll never miss "the good ole days": You'll get not only the pivot table you're looking for but the whole data set as well.

 

[happyhungarian]

I decided to use the Append method. However, what I'd like to do now is have some code that will automatically convert several tables into a connections. I recorded myself doing this manually and the result was the following. What I was hoping is that I could have code that would reference a list that contains the names of all the tables I would like to have converted to a connection. OR have the code simply reference the name of the tab and run this same code with the name of the activesheet in place of the current "MRR"

In this case, the table is called "MRR" (which so happens to also be the name of the worksheet that the table resides on). Is this possible??? I would then hope to have another piece of code that would say take ALL connections and Append them together



Sub Macro1()
'
Range("DT18").Select
ActiveWorkbook.Queries.Add Name:="MRR", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""MRR""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""ID"", type text}, {""Group"", type text}, {""Level"", type text}, {""Base Rate"", type number}, {""Column3"", Int64.Type}, {""Stage"", type text}, {""Task"", type text}, {""Task Stage"", type any}, {""WBS#"", type any}, {""Hours"", Int64" & _
".Type}, {""Start"", type datetime}, {""End"", type datetime}, {""Hour 1"", Int64.Type}, {""Hour 2"", Int64.Type}, {""Hour 3"", Int64.Type}, {""Hour 4"", Int64.Type}, {""Hour 5"", Int64.Type}, {""Hour 6"", Int64.Type}, {""Hour 7"", Int64.Type}, {""Hour 8"", Int64.Type}, {""Hour 9"", Int64.Type}, {""Hour 10"", Int64.Type}, {""Hour 11"", Int64.Type}, {""Hour 12"", Int6" & _
"4.Type}, {""Hour 13"", Int64.Type}, {""Hour 14"", Int64.Type}, {""Hour 15"", Int64.Type}, {""Hour 16"", Int64.Type}, {""Hour 17"", Int64.Type}, {""Hour 18"", Int64.Type}, {""Hour 19"", Int64.Type}, {""Hour 20"", Int64.Type}, {""Hour 21"", Int64.Type}, {""Hour 22"", Int64.Type}, {""Hour 23"", Int64.Type}, {""Hour 24"", Int64.Type}, {""Hour 25"", Int64.Type}, {""Hour " & _
"26"", Int64.Type}, {""Hour 27"", Int64.Type}, {""Hour 28"", Int64.Type}, {""Hour 29"", Int64.Type}, {""Hour 30"", Int64.Type}, {""Hour 31"", Int64.Type}, {""Hour 32"", Int64.Type}, {""Hour 33"", Int64.Type}, {""Hour 34"", Int64.Type}, {""Hour 35"", Int64.Type}, {""Hour 36"", Int64.Type}, {""Hour 37"", Int64.Type}, {""Hour 38"", Int64.Type}, {""Hour 39"", Int64.Type}" & _
", {""Hour 40"", Int64.Type}, {""Hour 41"", Int64.Type}, {""Hour 42"", Int64.Type}, {""Hour 43"", Int64.Type}, {""Hour 44"", Int64.Type}, {""Hour 45"", Int64.Type}, {""Hour 46"", Int64.Type}, {""Hour 47"", Int64.Type}, {""Hour 48"", Int64.Type}, {""Cost 1"", Int64.Type}, {""Cost 2"", Int64.Type}, {""Cost 3"", Int64.Type}, {""Cost 4"", Int64.Type}, {""Cost 5"", Int64." & _
"Type}, {""Cost 6"", Int64.Type}, {""Cost 7"", Int64.Type}, {""Cost 8"", Int64.Type}, {""Cost 9"", Int64.Type}, {""Cost 10"", Int64.Type}, {""Cost 11"", Int64.Type}, {""Cost 12"", Int64.Type}, {""Cost 13"", Int64.Type}, {""Cost 14"", Int64.Type}, {""Cost 15"", Int64.Type}, {""Cost 16"", Int64.Type}, {""Cost 17"", Int64.Type}, {""Cost 18"", Int64.Type}, {""Cost 19"", " & _
"Int64.Type}, {""Cost 20"", Int64.Type}, {""Cost 21"", Int64.Type}, {""Cost 22"", Int64.Type}, {""Cost 23"", Int64.Type}, {""Cost 24"", Int64.Type}, {""Cost 25"", Int64.Type}, {""Cost 26"", Int64.Type}, {""Cost 27"", Int64.Type}, {""Cost 28"", Int64.Type}, {""Cost 29"", Int64.Type}, {""Cost 30"", Int64.Type}, {""Cost 31"", Int64.Type}, {""Cost 32"", Int64.Type}, {""C" & _
"ost 33"", Int64.Type}, {""Cost 34"", Int64.Type}, {""Cost 35"", Int64.Type}, {""Cost 36"", Int64.Type}, {""Cost 37"", Int64.Type}, {""Cost 38"", Int64.Type}, {""Cost 39"", Int64.Type}, {""Cost 40"", Int64.Type}, {""Cost 41"", Int64.Type}, {""Cost 42"", Int64.Type}, {""Cost 43"", Int64.Type}, {""Cost 44"", Int64.Type}, {""Cost 45"", Int64.Type}, {""Cost 46"", Int64.T" & _
"ype}, {""Cost 47"", Int64.Type}, {""Cost 48"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Workbooks("Program P&L Template v3.3withPQ.xlsm").Connections.Add2 _
"Query - MRR", "Connection to the 'MRR' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=MRR;Extended Properties=""""" _
, "SELECT * FROM [MRR]", 2
End Sub

 

[Misca]

I don't understand the purpose of your macro: Usually it's either macros or queries and it's very rare you need them both.

You can append as many queries as you want to. The easiest (?) way would be if you turned your current query into a function and used another query to list all the tables in all your workbooks (If you can't use the folder approach you can always list the workbook names in a table and use that as the source for the list query.

If you're not familiar with how to turn queries into functions there's lots of great video tutorials in YouTube. Here's one of them: https://youtu.be/_csX8sCzJd0

 

[happyhungarian]

Ya I had a feeling it would be a bit confusing. The reason I need this is I have separate macro that takes a list of names and creates new tabs based off of that list. The tabs are created based off of a “template” tab so it essentially replicates that tab several times and then renames each tab using the names that are in that list. Where this code comes in is there is a table on that “template” tab so the table also gets replicated and then renamed to match the name of the tab. Now, I would like to automatically turn each table into a connection with a macro rather than having to go into each table and doing it manually. My plan is to use the “append” get data tool to merge all the tables together in a summary sheet but first I need all the tables to be turned into a “connection”. I hope that long winded explanation helped!