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)
'*****************************************************************************
________________
Hi Christian. I’m thinking it would be better to ask this in a help forum, and also set out a bit more on what you are trying to achieve, because this code might be overkill for your needs. Also, I suggest you look at http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables where I helped someone pull together data from seperate tables on different sheets into one. This could then be used to drive a pivot table.
Failing that, someone over at http://chandoo.org/forums (perhaps me) will be only too glad to take a look. Just be sure not to cross-post, and also upload a sample workbook with non-confidential data and post a link to it on the forum.
I used the “plug and play” download and was able to create my pivot table easily. One issue that I’m having is that I want to total dollars by customer and all i’m getting is “$-” in the table. When I change this to “Count” I get a number, but when I ask it to “Sum” it will not. Is there a fix?
Dear Debra,
I have 3 points. The 1st is a ‘thank you’ from me to you’ & the other 2 are questions. Having never used VBA before joining my current company I’ve been on a vertical (overhanging?) learning curve for the past 18 months. The reality is that without VBA I would not be able to do my job (I create a full set of Management Reports monthly in Excel – but that’s another story). Your website is one of my ‘reference points’ and I have learned a lot for which I am exceptionally grateful. I’m therefore constantly ‘pushing the boundaries’ of what I know so there are large gaps in my knowledge as my learning has not been structured other than by necessity. I think I will always use VBA but now need to learn a little about SQL.
I’ve reviewed the code in this spreadsheet & understand most of it but not the elements dealing with the data connections/SQL, etc. My 1st question. I’ve found lots of generic SQL courses on the web & have bought a currently indecipherable ‘dummies’ SQL book but are there any resources (web/books/course) that specifically handle this sort of data management (i.e. managing excel data rather than ‘pure’ Db stuff like Access)?
My 2nd question, a previous ‘blogger’ mentioned using named ranges rather than worksheets. I would like to do this as I have data sheets with additional information which I don’t want to consolidate (FX/reconciliations, etc). So, is it possible to consolidate only the data area of an Excel table and if so what’s the syntax (I’ve tried & failed).
Regards
Dear Kirill Lapin, Hector and Deborah!
Thank you very much for sharing your experience.
You guys save my life 🙂
all of you are Awesome!
Thanks again and keep doing what you are doing so gggreat!
GN
Hi All,
Spent some time to get this work, brilliant option to create a pivot with multiple worksheets.
I just encountered a problem with next step, to create Slicers for the pivot.
Unfortunately Slicer option is “Greyed” for pivots created using connection option but if I create a pivot directly from a data worksheet, it’s available. The file is saved as a .xlsm in Excel 2010.
Any suggestions please.
Hi Perera
I have the same problem. I’m thinking it has something to do with the type of pivot table it creates (an older version) but I have been unable to upgrade to the excel 2010 version of pivot tables.
Any help would indeed be welcome
I got it to work by adding this
Set PC = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal)
Set PC.Recordset = RS
Set PT = PC.CreatePivotTable(TableDestination:=ActiveSheet.Range(“A13”))
to
Set PC = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion14)
Set PC.Recordset = RS
Set PT = PC.CreatePivotTable(TableDestination:=ActiveSheet.Range(“A13”))