PIVOT table autofilter based on data validation cell selection

[fredstyler]

Hi,

I have a question about pivot tables

I attached a simplified example, question:

how can I link the value of the drop down box to the PIVOT table, so that the PIVOT tables shows the selection of the drop down box, without manually selecting it in the PIVOT table itself,

Thanks in advance

[DonkeyOte]

Using your example, right click on Sheet1 select View Code and paste below into resulting window.
Thereafter alterations to B2 should update the Pivot accordingly.

Note: when saving you must save in a macro enabled format and remember to enable Macros when re-opening going forward.

If using XL2010 (unknown) consider using a Slicer.

Please Login or Register  to view this content.

[fredstyler]

Thanks!

I am not so familiar yet with VBA in Excel, but do you know how it looks like if you have several PIVOTS on sheet 2 and 3, but the selection of countries remains on sheet 1?

[DonkeyOte]

Yes, but you need to provide some more information ... are you update all Pivots on Sheets 2 & 3 ?

The Change event will still reside on Sheet1 but obviously you need to iterate the Pivots on other sheets.

Roughly speaking:

Please Login or Register  to view this content.

however, this does not account for multi page selections etc... which may or may not be a requirement.

post a sample to demonstrate requirements as nec.

[fredstyler]

Hi,

The simplified version looks like this (attachment). There are more pivots on sheet2 (Pivots), but they all relate back to the country which is selected on the sheet1 (Country). The data comes from the same file, but is collected on the sheet called Data.

Thanks for all your help

[DonkeyOte]

I believe the code provided previously does what you requested, however, you obviously need to modify the Sheets Array to reflect your own set up - I used Sheet2 & Sheet3 as these were referred to in prior post.

The code (provided previously and amended per above) should reside in Country Sheet Object in VBE (ie location of Data Validation cell)