VBA for filtering pivottable based on cell

Hi,

Do you have an idea how I can filter automatically a pivot table by getting data from a cell and thus using this data to filter the pivottable?

For example, on cell A1 I have EU, and I want to use a region filter for a pivot table. How can the pivot automatically detect that A1 is EU and therefore automatically filter the pivottable to EU values only?


Thanks in advance!

vasco

Actually, now I realized that it would be much better to get the same code for a slicer rather than a series of filters. Any idea?

Vasco

Record a Macro while you filter your data like you want.
Then read the macro and change what is in the macro to the cell range.

Change the cells value, run the macro, watch the pivot table filter change.
You may need to convert the cell's value to the same type in the pivot table.

Here is some code that I use for this.

Please Login or Register  to view this content.

Hope this helps. The slicer is very cool in Excel 2010 but it only picks a single value. Filters, expecially date filter are VERY, VERY cool.

I am attempting to do the same thing but your code does not seem to be working for me. The tab I want my cell reference on is Report!A9. The filter in my pivot is on field Department. So, I would like my user to be able to type in their department in cell A9 on the report tab and click my macro button and filter the data in the pivot on the pivot tab. This data then automaticly flows into my report tab.