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