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)
'*****************************************************************************
________________
@Maria…my bad: Update_Pivot_Connection is the wrong name …I should have put ReestablishConnection
I’ve updated my code since I first posted here. To try my approach, you need to add these two subs to the workbook:
Copy this first sub into the ThisWorkbook module:
Option explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then Exit Sub
Application.EnableEvents = False
Call save_file
Application.EnableEvents = True
Cancel = True
Call ReestablishConnection
End Sub
and copy this into a standard module:
Sub save_file()
‘Working in Excel 2000-2010
Dim fname As Variant
Dim FileFormatValue As Long
Dim FilterIndexValue As Long
‘Check the Excel version
If Val(Application.Version) < 9 Then Exit Sub
If Val(Application.Version) < 12 Then
‘Only choice in the “Save as type” dropdown is Excel files(xls)
‘because the Excel version is 2000-2003
fname = Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Name, _
filefilter:=”Excel Files (*.xls), *.xls”, _
Title:=”Save as…”)
If fname False Then
‘We use the 2000-2003 format xlWorkbookNormal here to save as xls
ActiveWorkbook.SaveAs fname, FileFormat:=-4143, CreateBackup:=False
End If
Else
‘Need to get excel to pre-set the filterindex setting to match the existing file, rather than have a hard-coded value.
‘For some reason if the current extension doesn’t match the default filterindex setting then
‘the activeworkbook.name is returned in quotation marks, and whatever the user selects from the filterindex
‘dropdown has no effect unless those quotation marks are removed.
Select Case LCase(Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) – InStrRev(ActiveWorkbook.Name, “.”, , 1)))
Case “xlsx”: FilterIndexValue = 1
Case “xlsm”: FilterIndexValue = 2
Case “xls”: FilterIndexValue = 3
Case “xlsb”: FilterIndexValue = 4
Case Else: FilterIndexValue = 3 ‘make this the default in the event that we get no match.
End Select
‘Give the user the choice to save in 2000-2003 format or in one of the
‘new formats. Use the “Save as type” dropdown to make a choice,Default =
‘Excel Macro Enabled Workbook. You can add or remove formats to/from the list
fname = Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Name, filefilter:= _
” Excel Macro Free Workbook (*.xlsx), *.xlsx,” & _
” Excel Macro Enabled Workbook (*.xlsm), *.xlsm,” & _
” Excel 2000-2003 Workbook (*.xls), *.xls,” & _
” Excel Binary Workbook (*.xlsb), *.xlsb”, _
FilterIndex:=FilterIndexValue, Title:=”Save as…”)
‘Find the correct FileFormat that match the choice in the “Save as type” list
If fname False Then
Select Case LCase(Right(fname, Len(fname) – InStrRev(fname, “.”, , 1)))
Case “xls”: FileFormatValue = 56
Case “xlsx”: FileFormatValue = 51
Case “xlsm”: FileFormatValue = 52
Case “xlsb”: FileFormatValue = 50
Case Else: FileFormatValue = 0
End Select
‘Now we can create/Save the file with the xlFileFormat parameter
‘value that match the file extension
If FileFormatValue = 0 Then
MsgBox “Sorry, unknown file extension”
Else
‘Save the file in the format you choose in the “Save as type” dropdown
ActiveWorkbook.SaveAs fname, FileFormat:= _
FileFormatValue, CreateBackup:=False
End If
End If
End If
End Sub
Hopefully that will do the trick. Let me know if you have any problems with this. I can email you a workbook if you like…you can email me at weir dot jeff at gmail dot com
Note you’ll have to replace the curly quotation marks with straight ones (the wordpress parser changed them to the wrong ones). I’ll upload a workbook with this code and paste a link here, after I’ve checked it works.
Have posted an amended version of Kirill’s spreadsheet incorporating some code that amends the connection when someone uses Save As. That way, if they make some changes before reopening the file, and refresh the pivot, the changes will be incorporated.
http://cid-f380a394764ef31f.office.live.com/view.aspx/.Public/VBA.MULT%5E_CONS%5E_PIVOT%5E_EN%204.xls
Hi All, Back Again!This macro has been working wonderfully for me. And management at work love the ability to see every month of data consolidated into one pivot table. However, I received an error and I am not sure how to fix. I just added my ninth tab to my excel file. There will only be 12 (one for each month) and the macro has stopped working. It stops at the following line in the code: .Worksheets(arrSheets).Copy
It came from the following subset:
With ThisWorkbook
strPath = .Path
strFile = .FullName
strFileTemp = strPath & “DBtemp” & Format(Now, “yyyymmddhhmmss”) & strFileExt
ActiveSheet.Cells.Clear
.Worksheets(arrSheets).Copy
End With
I never had the problem before so I’m not sure why it is happening now. I am using MS Excel 2003 on Windows XP operting system at work. Any help would GREATLY appreciated.
Thanks!!
Ni Marie,
Could you please confirm what is the error you get when the macro stops and also show the arrSheets setting part you’re using in your code, you know, this line:
arrSheets = Array(“Ontario”, “Alberta”)The problem must be there.
I’ve had this on my computer for a while and had the opportunity to use it today. Very nice!