Pivot table and macro Synchronize pivot tables
Posted by Nona Sloven on May 07, 2001 8:49 AM
Hi:
I have made three pivot tables in Excel97. The second and third I made them based on the first one and they are in the same workbook; also, the three have exactly the same fields and format, and also made with the same data. Does any one of you know how to synchonize my three pivot tables so that when I select a specific page in the first table (which is the main pivot table), the second and third be selected with the same item or page. I mean, if I select and item in the drop down list, say, Cab, I need the other pivot tables be selected by the item Cab automatically in their drop down lists and not that they have to be selected manually; although I also should be able to select and item manually in each of them. One last consideration is that the fields to be synchronized are the same cells F12 and I12 in the three tables. They should work either if I select F12 or If I select I12. I know this is only possible with a macro because you cannot enter a formula in a pivot table field. If any one of you knows how to come up with this please help. Thanks.
Nona Sloven
Posted by A McKenzie on May 08, 2001 10:30 AM
I have just recently tried to find a way to deal with a similar scenario - as long as your page fields are the same, this can be accomplished thru using a macro button - you have to write the code (or manually record the macros) to let the page field for each table equal what you want, in your example "cab". this is made even easier if you name your pivot tables. once you get one table's code written you can simply copy/paste it, changing only the pivot table name. if you do not know how to write this, let me know and i can help you out.
Posted by Nona Sloven on May 09, 2001 5:23 AM
Hi:
Thank you for your response.
I have no idea how to write a code for this. My knowledge at writing on BVA is very poor. Let me tell you however, the tables should be synchronize not only by the item "cab" but by any in a list of around ten items in the drop down lists in each of the pivot table. Do you insist you can handle this? Thank you.
Nona.
Posted by A McKenzie on May 09, 2001 6:07 AM
you would have to code and creat an associated macro button for each choice - but yes, it can be done. i just finished a project with fifteen different tables, all with 13 items on the drop down lists. if you don't have any knowledge of VBA though, you could still do it with macros. my project will be re-used quite often, so it was worth the work i put into it. if your project will not be used often, sadly enough the best bet is to leave it manual.
