Create pivot table from multiple sheet data
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 (109 downloads)Leave a Reply
Want to join the discussion?Feel free to contribute!
I want to join in this IT CHAT
Dear Venkat, what do you want?
i want know about macros from basics