Question

create an excel macro to combine at least 16 tables in the same workbook with the same column headings to form one master table. There are other worksheets in the workbook, but not all of the sheets will be copied to the master.

Answer 1

Pre req:1.Make sure your tabels are formatted correctly

2.all the tables column name shoud be same

3.Developer permissions to add macros/create macros

Steps:

Step1:Select the sheet which contains table :go to Data tab in Excel header section ->select any cell from table ->Select from table/range tab

Step2:This will bring up power query editor tab

Step:In the power query editor go to home page->select close & load dropdown->choose close & load To

Step4:Import Data window will be displayed->select Only create Connection radio button->click on ok

Step5:Queries and connection pane will be displayed for created table on the right hand side

Step6:Do this for all the tables and create connection

Step7:Select My Macro tab->click on PQ Connect All tables-> select yes from the popup->This will create all the connections in the workbook

Step8:Select Data tab->Queries and connections->combine queries->Append->In Append window select 3 or more tables radio button->Select the required tables for which you craeted connections ->click on ADD->Click okay

Step9:All the Tables will be combined together!!

Open ur excel with latest version click ALT+F11 then select developer tab vb editor->Macros->PQ connect all tables

and paste this code

Option Explicit

Sub Add_connection_All_Tables()

Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim sName As String
Dim sFormula As String
Dim wq As WorkbookQuery
Dim bExists As Boolean
Dim vbAnswer As VbMsgBoxResult
Dim vbDtaModel As VbMsgBoxResult
Dim i as Long
Dim dStart As Double
Dim dTrim AS Double

vbAnwer = MsgBox("Do you want to run the macro to create for all the tables selected"?)

If vbAnswer = vbYes Then

vbDataModel = MsgBox("Do you want to add the data to the Data model")

dStart = Timer
Set wb = ActiveWorkbook

For Each ws In ActiveWorkbook.Worksheets
For Each lo In ws.ListObjects

sName = lo.Name
sFormula= "Excel.CurrentWorkbook()({Name=""" & sName & """}}[Content]"

bExists = False
For Each wq In wb.Queries
if Instr(1, wq.Formula, sFormula) > 0 Then
bExists = True
End if
Next wq

If bExists  False Then

wb.Queries.Add Name:=aName,
Formula:="let" & Chr(13)& "" & Chr(10) & " Souce = Excel.CurrentWorkbook()

'Add Connection

vb.Connection.Add2 Name:="Query - " & aName,
Description:="Connection to the '" & sName
ConnectionString:"OLEDB;Provider=""
CommandText:"" & sName & "", _
lCmdtype:",_
CreateModelConnection:=False,_
Import Relationships:=False

Add to ADD model

if vbDatamodel = vbyes Then
vb.Connection Add2 Name:="Query - " & aName,
Description:="Connection to the '" & sName
ConnectionString:"OLEDB;Provider=""
CommandText:"" & sName & "", _
lCmdtype:",_
CreateModelConnection:=False,_
Import Relationships:=False

End If

i=i+1
End if

Next lo
Next ws

dTime = Timer = dstart

Msgbox i & " Connections have been craeted in "& Format(dTime,"0.0)

End If
End Sub