Consolidate Pivot tables wth similar data

Hi,

Are all the pivots based off the same source table or do you have one source table for each plan?

Can you change that so that all the data is in one table? Otherwise it will make your desired goal much harder to achieve.

I fear that is really not a good layout for the kind of reporting you want to do. Do you have Power Query available to you?

In that case, I would suggest using code to consolidate the tables into one new sheet that can then be used as the source for all the pivot tables. That way you may report on a per plan or overall basis. If you need some code for that I would require a workbook that is accurately representative of the layout you have currently though the actual data can, and should, be censored.

I do not seem to be able to edit my post for some reason, so I will add that if the previous workbook you posted is accurate, I can probably work with that to create a sample.

Do you have access to a file sharing location like Box or Dropbox?

Thanks. I am out the door in a minute but will endeavour to take a look tomorrow.

Thanks. Really appreciate your help

It would appear you don't have the same source data columns for each table, which complicates things a little. It may prove simplest to build a table from the pivots for each plan and use that for the consolidated pivot tables. Is it OK to either move the pivot tables so that they are always located in the same column, or to rename them for easier identification in the code?

OK. Do you need the data from every pivot table on each sheet?

OK. Here is your workbook with some code to create a summary table, which I have attached to your refresh pivot tables button. It collates all the data from the other pivot tables into a summary pivot data sheet and then refreshes the first pivot table on the Summary worksheet, which I have updated to include the location and plan information.

https://drive.google.com/file/d/0Bxr...ew?usp=sharing

Let me know if you have any questions.

No- that is my fault. Please amend the code in Sheet1 to this version

Please Login or Register  to view this content.

which should fix the problem with the dates converting to US format in the code.

On which line? I have tested it a couple of times without error on the file you linked to just now.

It shouldn't be version dependent that I can think of. Where is the error occurring?

It looks like you're running that over Citrix? Do you have direct access to a machine with 2003 on it?

I don't think I currently have 2003 installed on a VM anywhere for testing.