This article will show you how to Create pivot table from multiple sheet data by using VBA. We will be using SQL query and connection to read data from all tabs.

If you dont know how to open connection with excel by using ADODB object.  Read my earlier article How to read excel data by ADODB Connection

How this works?

Code below is creating a query by looping on all tabs. then we open a recordset with adodb connection. it will read data from all tab and combine them in one tab as recordset.

Code is using recordset and pivotcache data source and creating pivot tab.

Note – This pivot table can not be refreshed as normal pivot do. but this code can refresh that pivot table too.

Sub CreatePivotFromMultipleTabs()

 Dim objRst As Object
 Dim strQuery As String
 Dim wksSheet As Worksheet
 Dim objPivot As PivotTable
 Dim objPivotCache As PivotCache

 ‘– Create Query to Union data from all tabs.
 For Each wksSheet In ThisWorkbook.Worksheets
 If wksSheet.Name <> “Pivot” Then
 If strQuery = “” Then
 strQuery = “Select * From [” & wksSheet.Name & “$]”
 Else
 strQuery = strQuery & ” Union All ” & “Select * From [” & wksSheet.Name & “$]”
 End If
 End If
 Next wksSheet 

 ‘–Open connection with excel
 OpenConnection ThisWorkbook.FullName, ExcelFile2007, DataHasHeader

 ‘– Create a recordset open to store data from all tabs.
 Set objRst = CreateObject(“ADODB.Recordset”)

 ‘–Open recordset
 objRst.Open strQuery, objConnection

 ‘– Create pivotcache by using the recordset data
 If ThisWorkbook.PivotCaches.Count = 0 Then
 Set objPivotCache = ThisWorkbook.PivotCaches.Create(xlExternal)
 Else
 Set objPivotCache = ThisWorkbook.PivotCaches(1)
 End If

 Set objPivotCache.Recordset = objRst 

 ‘– CreatePivot table
 If Sheet5.PivotTables.Count = 0 Then
 Set objPivot = objPivotCache.CreatePivotTable(Sheet5.Range(“A1”))
 End If

 ‘–Refresh All.
 ThisWorkbook.RefreshAll
CloseConnection

  End Sub

download a working file from here

Multiple Sheet data pivot table (35 downloads)