Can't create a Master Pivot table from multiple worksheets in the same workbook

[dwhite30518]

I am trying to create a macro that would create a pivot table from the data from different worksheets. I tried creating a pivot table using the "ALT+D+P" wizard but I can't get the pivot table formatted that way I would like to. Is there a way to create a macro that would create a master pivot table using the data from the worksheets in this workbook and in the same format as the pivot tables on the individual worksheets? Look forward to your assistance. I have attached the sample workbook I have been using for all my my testing and creating macros.

Thanks!

[JosephP]

you can create a pivot table from multiple consolidation ranges. don't use any page fields and don't include the RSA column in the ranges, and then add a calculated item (not field) to the column field to create your third column.

[dwhite30518]

Except, I want to be able to drill down and see which RSA's are open and the QTY stilled for any Open RSA relating to the Item Number.

[dwhite30518]

I want to do this without having to scroll through all of the worksheets and figuring out what is still owed.

[JosephP]

in that case you will need to actually create one table with all your data. personally I would recommend that anyway since it will make all reporting much simpler.

[dwhite30518]

That is what i am trying to do but when I use the wizard with the shortcut keys "Although+D+P", the format is not right...so how do I create this "master" pivot table?

[JosephP]

I mean physically create one table with all your source data in it, not one pivot table.

[dwhite30518]

Are you saying to copy the data from each worksheet to a "master" worksheet and then create a pivot table from that?

[JosephP]

yes, I am. always better to have one table for reporting purposes if at all possible. you can always filter it to view specific RSAs for example.

[MarvinP]

Hi dwhite30518,
See if this code isn't what you need to put your data on the Summary (first) worksheet.

Please Login or Register  to view this content.

[dwhite30518]

That is what I am trying to create...One pivot table for reporting. There must be another way to do the same thing without having to manually copy each worksheet and then paste to a master worksheet. It would take a while considering the number of worksheets I am working with as well as the fact of having to copy data all over again every time I make a change to the original worksheet. There has to be a way to create a macro that will get the data from all of the worksheets and then create the pivot table in a similar format as the pivot tables on the individual worksheets.

[dwhite30518]

Marvin,

If I am reading this code right, this code will copy the data from one of the worksheets and then paste to the "Summary" worksheet...right?

[MarvinP]

Hi,

Have you heard of DNR? In your code for the pivot table you can use named ranges for your data instead of needing to select each individually. I thought you wanted to copy all the data to the first sheet. My code above will do that.

See http://www.gilliganondata.com/index....your-charts-2/ or
http://excelnoob.blogspot.com/2010/0...ed-ranges.html

Perhaps you need this Excel tool to accomplish your task.

[JosephP]

dynamic named ranges will still involve a consolidation range-based pivot table and that won't work. your options as I see them are
1. have one table of data. this is, imo, the best option (I would replace the other sheets completely)
2. use ado to extract the data from each sheet into a recordset and populate the pivot table with that. however there are memory leak errors when running sql commands on an open workbook.

I don't think powerpivot would allow you to construct a union query to get all the data in one table but I could be wrong.

[MarvinP]

Hey JosephP,

"Won't Work" is a little vague for me. If you consolidate all the data onto the first sheet using my above code then you don't have consolidation problems. Then you are only limited by what Pivot Tables can do. Is the "Won't Work" because you can't figure the VBA to do the entire problem with a single click?

[dwhite30518]

Let me clarify what I would like to do...I want to create a "Master" Pivot table on the summary worksheet that will act like a report showing me how many devices are due back to the customer. I would like to create this pivot table without having to copy each worksheet to a "Master" worksheet and create the pivot table from that. The individual worksheets get data added on a daily basis and if I use this copy method, it would just be time consuming. Is there a way that I can take the ranges from each worksheet and create a single pivot table on the summary tab using a VBA and have a refresh button to refresh the data every time I add data?

[MarvinP]

Hey,

Clicking a button to run the macro I gave you might take a minute to accumulate all the sheets to the first. Your "too much time" comment is a little harsh. It will take you 100 times longer to create a VBA macro dealing with multiple sheets and multiple ranges than running my macro to get them into a single table. You can modify my macro to clear the contents of Sheet1 before you start.

I think you are thinking you need to manually copy and paste each sheet. Just run my macro and it will do ALL sheets. It will be fast. I can speed it up using Application.ScreenUpdating = False if you think it takes too long.

[JosephP]

@Marvin
"Won't work" related to your comment about dynamic named ranges, not your code (since I've already said that one table of data is needed)

@dwhite30518,
your requirement is clear, I think- that is what we have been trying to address. barring the ADO (or possibly querytable) option, you will have to copy the data if a pivot table based on consolidation ranges won't do, which you have indicated it won't. why is automated copying of the data too time consuming?

[dwhite30518]

My biggest concern, is making sure that I can try and keep this file as small as possible but still have all of the relevant data.

I am using the pivot tables as part of the formatting of the workbook so deleting them I don't think would be a good idea.

A couple of things with the code you provided previously...

1. If I wanted this code to hide the "Master" sheet with this data, how can I do that?

2. Is there a way to refresh this data when changes are made or do I just need to add code to clear the sheet and then copy the data?