A common pivot table question is “How can I create a pivot table from data that’s on separate sheets in my workbook?
Sometime people have a workbook set up with a separate sheet for each region, or for each salesperson.
Eventually, they want to pull all the data together, and create a summary report in a pivot table, from multiple sheets.
Multiple Consolidation Ranges
Excel has a feature (well hidden Excel 2007) that lets you do this, using Multiple Consolidation Ranges.
A pivot table created this way has limited features, and isn’t much use in summarizing Excel data.
I usually recommend that you move all the data onto one worksheet, if it will fit, or store it in a table in Access, then use that as the source for the pivot table.
Create a Union Query
Another solution is to create a Union query from the separate tables, and use that as the source data.
Normal Pivot Table
With this solution, you’ll end up with a normal pivot table, with none of the limitations. However, it’s a bit tedious to set up, especially if you have more than a couple of tables.
Automate the Union Query
Instead of setting this up manually, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Héctor Miguel Orozco Diaz. (You might remember Héctor’s innovative Filter Pivot Table Source Data example, posted earlier this year.)
To adjust their sample code to work in your file, you’d replace the sheet names in the CreateConnection code.
To go to the CreateConnection code, right-click on the “Create Empty Table” button, and click Assign Macro, then click Edit.
You can also adjust the location where the pivot table will be added. This line is further down in the CreateConnection code.
After those small changes, save the code changes. Then go back to Excel, click the button on the worksheet, and a summary pivot table will be automatically created.
Download the Sample File
Thanks Kirill and Héctor, for making a complicated task easier. You can download their sample file from the Contextures website: PT0023 – Pivot Table from Multiple Sheets
(Also, please check the update section below, for a newer version of the file)
Update — December 2011
The solution described in this article was created as a conceptual prototype and targeted mainly advanced VBA users. The code has minimal error handling and compatibility checks.
Given the massive response from all kinds of users willing to adopt this solution in their own applications, we would like suggest a similar solution based on ADO.
Advantages:
- No need for temporary file generation
- The code is faster and less prone to errors
Disadvantages:
- No manual refresh of the PivotTable
- Need to rebuild connection from the scratch to update the cache with new data
Download the ADO Sample File
You can download the new ADO version of the file from the Contextures website: PT0024 – Pivot Table from Multiple Sheets – ADO version
Update — August 28, 2012
In the comments below, Kirill posted code that will automatically detect the sheet names. The blog formatting changed his minus sign to a long dash, and also deleted the Less Than Greater Than operator. Here is the correct code, with Kirill’s instructions:
In the code, replace this line:
' Sheets to consolidate
'*****************************************************************************
arrSheets = Array("310_BWATTS_P Pastujova", "310_BWATTS_Maria Sanchez")
'*****************************************************************************
with the following code:
' Sheets to consolidate
'*****************************************************************************
Dim ws As Worksheet
ReDim arrSheets(0)
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
arrSheets(UBound(arrSheets)) = ws.Name
ReDim Preserve arrSheets(UBound(arrSheets) + 1)
End If
Next ws
ReDim Preserve arrSheets(UBound(arrSheets) - 1)
'*****************************************************************************
________________
I’m having a problem with code that works fine in Excel 2003 & my versin of Excel 2007 – however, another site is trying to use the workbook and the CreatePivotTable command is returning a 1004 – Catastrophic Failure. Does anyone know why this is happening? Code below:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array( _
“OLEDB;Provider=MSOLAP;Initial Catalog=[OCWCube];Connect Timeout=0;PageTimeout=0;Data Source=” & ThisWorkbook.Path & “” & strDSNName & “.cub;CreateCube=CREATE CUBE [OCWCube](“, _
“DIMENSION [Nurse ID],LEVEL [All] TYPE ALL,LEVEL [Nurse ID], DIMENSION [Doctor ID],”, _
“LEVEL [All] TYPE ALL,LEVEL [Doctor ID],DIMENSION [Insurance ID],LEVEL [All] TYPE ALL,LEVEL [Insurance ID], DIMENSION [Charge Code ID],LEVEL [All] TYPE ALL,”, _
“LEVEL [Charge Code ID], DIMENSION [Diagnosis ID],LEVEL [All] TYPE ALL,LEVEL [Diagnosis ID], DIMENSION [Type Of Surgery],LEVEL [All] TYPE ALL,LEVEL [Type Of Surgery], DIMENSION [Charge Code Group], “, _
“LEVEL [All] TYPE ALL,LEVEL [Charge Code Group],DIMENSION [O Insurance ID],LEVEL [All] TYPE ALL,LEVEL [O Insurance ID], DIMENSION [PC Modifier],LEVEL [All] TYPE ALL,LEVEL [PC Modifier], “, _
“DIMENSION [Other Code],LEVEL [All] TYPE ALL,LEVEL [Other Code], DIMENSION [Facility ID],LEVEL [All] TYPE ALL,LEVEL [Facility ID], “, _
“DIMENSION [Service Date] TYPE TIME,LEVEL [All] TYPE ALL,LEVEL [Year] TYPE YEAR,LEVEL [Quarter] TYPE QUARTER,LEVEL [Month] TYPE MONTH, DIMENSION [POS], “, _
“LEVEL [All] TYPE ALL,LEVEL [POS], DIMENSION [TOS],LEVEL [All] TYPE ALL,LEVEL [TOS], DIMENSION [Acct Date] TYPE TIME, LEVEL [All] TYPE ALL,LEVEL [Year] TYPE YEAR,”, _
“LEVEL [Quarter] TYPE QUARTER,LEVEL [Month] TYPE MONTH, DIMENSION [Practice ID], LEVEL [All] TYPE ALL,LEVEL [Practice ID], DIMENSION [AC Modifier],LEVEL [All] TYPE ALL,”, _
“LEVEL [AC Modifier], DIMENSION [Surgeon ID],LEVEL [All] TYPE ALL,LEVEL [Surgeon ID], MEASURE [Total Nurse Time] FUNCTION SUM, DIMENSION [Trans ID],”, _
“LEVEL [All] TYPE ALL,LEVEL [Trans ID], DIMENSION [Patient],LEVEL [All] TYPE ALL,LEVEL [Patient],DIMENSION [Patient Sex],LEVEL [All] TYPE ALL,LEVEL [Patient Sex], “, _
“DIMENSION [Fin Class ID],LEVEL [All] TYPE ALL,LEVEL [Fin Class ID], DIMENSION [Patient Age],LEVEL [All] TYPE ALL,LEVEL [Patient Age], “, _
“DIMENSION [Zip Code],LEVEL [All] TYPE ALL,LEVEL [Zip Code], DIMENSION [Bill Type],LEVEL [All] TYPE ALL,LEVEL [Bill Type], MEASURE [Total Doctor Time] FUNCTION SUM,”, _
“DIMENSION [O Insurance Group],LEVEL [All] TYPE ALL,LEVEL [O Insurance Group], DIMENSION [O FinClass Id],LEVEL [All] TYPE ALL, “, _
“LEVEL [O FinClass Id], DIMENSION [Visit Reason],LEVEL [ALL] TYPE ALL,LEVEL [Visit Reason], DIMENSION [Visit User Defined],LEVEL [ALL] TYPE ALL,LEVEL [Visit User Defined], “, _
“DIMENSION [Anes User Defined],LEVEL [ALL] TYPE ALL,LEVEL [Anes User Defined], “, _
“DIMENSION [ASA Code],LEVEL [All] TYPE ALL,LEVEL [ASA Code], MEASURE [Charges] FUNCTION SUM, MEASURE [Payments] FUNCTION SUM, MEASURE [Adjustments] FUNCTION SUM, “, _
“MEASURE [Balance] FUNCTION SUM); InsertInto=INSERT INTO OCWCube([Charges],[Payments],[Adjustments],[Balance],[Nurse ID],[Doctor ID],[Insurance ID],”, _
“[Charge Code ID],[Diagnosis ID],[Type Of Surgery],[Charge Code Group],[O Insurance ID],[PC Modifier],[Other Code],[Facility ID],[Service Date],[POS],[TOS],[Acct Date],[Practice ID],[AC Modifier],”, _
“[Surgeon ID],[Total Nurse Time],[Trans ID],[Patient],[Patient Sex],[Fin Class ID],[Patient Age],[Zip Code],[Bill Type],[Total Doctor Time],[O Insurance Group],”, _
“[O FinClass Id], [Visit Reason], [Visit User Defined], [Anes User Defined], [ASA Code]) OPTIONS ATTEMPT_ANALYSIS SELECT QA.Charges, QA.Payments, QA.Adjustments, QA.Balance, QA.NurseId, QA.DoctorId, QA.InsuranceId, QA.ChargeCodeId, QA.DiagnosisId, “, _
“QA.TypeOfSurgery, QA.ChargeCodeGroup, QA.OInsuranceId, QA.PCModifier, QA.OtherCode, QA.FacilityId, QA.ServDate, QA.PlaceOfService, QA.TypeOfService, QA.AcctDate, QA.PracticeId, QA.ACModifier, QA.SurgeonId, “, _
“QA.TotalNurseTime, QA.TranId, QA.PatientName, QA.PatientSex, QA.FinClassId, QA.PatientAge, QA.PatientZIP, QA.BillType, QA.TotalDoctorTime, QA.OInsuranceGroup, QA.OFinClassId, QA.VisitReason, QA.VisitUserDefined1, QA.AnesUserDefined1, QA.ASACode “, _
“FROM BAMANALYSIS QA ” & strWhere & “; Source_DSN=””DSN=” & strDSNName & “;APP=Microsoftr Query;DATABASE=” + strDSNName + “;Network=DBMSSOCN””;UseExistingFile=false”)
.CommandType = xlCmdCube
.CommandText = Array(“OCWCube”)
.MaintainConnection = True
.CreatePivotTable TableDestination:=Range(“B1?), TableName:=”ptNHA”
End With
I started getting an error this week when adding my weekly table and recreating the pivot table. The error I get is:
Run-time error ‘1004’:
[Microsoft][ODBC Exel Driver] Query is too complex.
I don’t understand why I get this error. Each sheet is about the same size. I am using the following code to pick up all sheets (except my SQL tab that i use to dynamically create an SQL query I run to pull the data):
[code]
Dim ws As Worksheet
ReDim arrSheets(0)
For Each ws In ThisWorkbook.Worksheets
If ws.Name = “SQL” Then GoTo 1
If ws.Name ActiveSheet.Name Then
arrSheets(UBound(arrSheets)) = ws.Name
ReDim Preserve arrSheets(UBound(arrSheets) + 1)
End If
1: Next ws
ReDim Preserve arrSheets(UBound(arrSheets) – 1)[/code]
Any ideas? Did I somehow find the max allowable sheets (I don’t think so, others have reported more than 53 sheets).
As a followup, I verified I am not able to have more than 50 sheets. If I remove any sheet, the Pivot Table will be created correctly, but the 51st sheet results in the “Query is too complex” error.
I found a workaround (not pretty), which brought up related questions.
The issue I had was with 50+ Union All statements…SQL doesn’t allow that many. I was able to do them in sections, then Union them all together at the end. I had to build it to support more or less than 50, and now it appears it will work with as many tabs as Excel can hold.
The issue I would still like to solve is related to run time…the generation of a temp file that has 50+ sheets takes minutes, thus causing this Macro to run long. I tried doing away with temp file, but I couldn’t hold a connection to the current file…and I don’t know enough to understand why.
Here is the semi-final code I am using, which works(slowly).
Sub CreateConnection()
Dim PT As PivotTable
Dim PC As PivotCache
Dim strFile As String
Dim strFileTemp As String
Dim strPath As String
Dim arrSheets As Variant
Dim strSQL As String
Dim strSQLtemp As String
Dim strCon As String
Dim i As Long
Dim objRS As Object
‘ Sheets to consolidate
‘*****************************************************************************
‘arrSheets = Array(“Ontario”, “Alberta”)
Dim ws As Worksheet
ReDim arrSheets(0)
For Each ws In ThisWorkbook.Worksheets
If ws.Name = “SQL” Then GoTo 1
If ws.Name ActiveSheet.Name Then
arrSheets(UBound(arrSheets)) = ws.Name
ReDim Preserve arrSheets(UBound(arrSheets) + 1)
End If
1: Next ws
ReDim Preserve arrSheets(UBound(arrSheets) – 1)
‘ Sheets to consolidate
‘*****************************************************************************
If Val(Application.Version) > 11 Then
DeleteConnections_12
CheckFileFormat_12
Else
strFileExt = “.xls”
lngFileFormat = xlNormal
End If
Application.ScreenUpdating = False
With ThisWorkbook
strPath = .Path
strFile = .FullName
strFileTemp = strPath & “DBtemp” & Format(Now, “yyyymmddhhmmss”) & strFileExt
ActiveSheet.Cells.Clear
.Worksheets(arrSheets).Copy “‘This takes a LONG time
End With
With ActiveWorkbook
.SaveAs strFileTemp, lngFileFormat “‘This takes a LONG time
.Close
End With
‘strSQL = “”
For i = LBound(arrSheets) To Application.WorksheetFunction.Min(i + 45, UBound(arrSheets))
If arrSheets(i) ActiveSheet.Name Then
If strSQL = “” Then
strSQL = “(SELECT * FROM [” & arrSheets(i) & “$]”
Else
strSQL = strSQL & ” UNION ALL SELECT * FROM [” & arrSheets(i) & “$]”
End If
End If
DoEvents
Next i
strSQL = strSQL & “) a”
strSQL = “Select * from ” & strSQL
“”‘Next section accounts for sheets > 45″”‘
Do While i <= UBound(arrSheets)
strSQLtemp = “”
If i <= UBound(arrSheets) Then
For i = i To Application.WorksheetFunction.Min(i + 45, UBound(arrSheets))
If arrSheets(i) ActiveSheet.Name Then
If strSQLtemp = “” Then
strSQLtemp = “(SELECT * FROM [” & arrSheets(i) & “$]”
Else
strSQLtemp = strSQLtemp & ” UNION ALL SELECT * FROM [” & arrSheets(i) & “$]”
End If
End If
DoEvents
Next i
strSQLtemp = strSQLtemp & “) b”
strSQL = strSQL & ” UNION ALL ” & “Select * from ” & strSQLtemp
End If
Loop
“”End of sheets > 45″”‘
strCon = _
“ODBC;” & _
“DSN=Excel Files;” & _
“DBQ=” & strFileTemp & “;” & _
“DefaultDir=” & strPath & “;” & _
“DriverId=790;” & _
“MaxBufferSize=2048;” & _
“PageTimeout=5?
MsgBox ThisWorkbook.PivotCaches.Count
Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
With PC
.Connection = strCon
.CommandType = xlCmdSql
.CommandText = strSQL
Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16?))
PT.Name = “TestPivot”
End With
With PT.PivotCache
.Connection = Replace(strCon, strFileTemp, strFile)
End With
‘Clean up
Kill strFileTemp
Set PT = Nothing
Set PC = Nothing
End Sub
Joshua,
this is great and helped me out a ton. I’m combining sheets from different files but this workaround worked really well for me.
I pull in sheets from 100+ files in less than ten seconds. I don’t copy the sheets, I just read them. So maybe that’s why it’s so much faster.
How do you “just read them”? I am a bit of a hack when it comes to VBE…I can reverse engineer and solve, but I don’t have all the knowledge I need….I would prefer to just read them, copying them then deleting the copy is a comprise I make to get the job done (and it takes about 5 minutes more!)