Creating a pivot table from the entire workbook

[tazz]

Hello all,
I would like to create a Pivot table using data from the entire workbook.
My data is in every sheet at B26:F41 and I want my Pivot table on sheet”Report”(same workbook) in col O, P &Q.
The number of sheets and their name is unknown.
Any ideas will be very helpful.

Thank you

 

[Luke M]

Would this method work?
http://blog.contextures.com/archives/2011/12/28/excel-pivot-table-from-multiple-sheets-update/

 

[tazz]

The method provided in that site is dificult and there are no guarantee that al the updates of the table will work or if it will handle large amount of spreadsheets.
Because of this I have decided for a different approach:
1. Copy all data into a different spreadsheet inside the workbook
2. Create a pivot table from that spreadsheet.
I made some research and I found a vba code that brings all needed data in a separate sheet and is working really nice.
If you like this approach please let me know and I will post a link for the site that host the code.
Thank you all.

 

[SirJB7]

Hi, tazz!
Maybe you want to share your solution with the community, so as people who read this would have the issue or question and the solution or answer as well.
Regards!

 

[tazz]

Hello all,
These are the steps to have a Pivot Table using data from the entire workbook without having this Pivot table taking data direct from each sheet:
1. Open your workbook
2. Go to http://msdn.microsoft.com/en-us/library/cc793964(v=office.12).aspx and copy the code
3. Alt+F11, create a module in your workbook and paste the code inside the module.
4. Select Run macro.
A new spreadsheet(RDBMergeSheet) will be created and your data from all other sheets will be copied here. Please read the code and you will see that you have the option to select rows with data needed to be transferred.
Now you can create a Pivot Table using data from RDBMergeSheet.
Regards!