VBA to filter multiple pivot tables on text in a referenced cell

Hi All,

Recently, I created a dashboard in Excel to visualize performances on several metrics. Using pivot tables, I have been able to easily combine, visualize and transpose data.
For this dashboard, I want to be able to type in a product name and click on a button (macro) to update all pivot tables (by update, I mean to filter values of the product of interest only).

Earlier attempts have not payed of yet:

---------------------------------------------

> Range("L1").Select
Selection.Copy
ActiveSheet.PivotTables("PIVOTTABLE1").PivotFields("LABEL1"). _
ClearAllFilters
ActiveSheet.PivotTables("PIVOTTABLE1").PivotFields("LABEL1").CurrentPage _
= "xlPasteValues"

> ActiveSheet.PivotTables("PIVOTTABLE1").PivotFields("LABEL1"). _
ClearAllFilters
ActiveSheet.PivotTables("PIVOTTABLE1").PivotFields("LABEL1").CurrentPage _
Range("L1")

> ActiveSheet.PivotTables("PIVOTTABLE1").PivotFields("LABEL1"). _
ClearAllFilters
ActiveSheet.PivotTables("PIVOTTABLE1").PivotFields("LABEL1").CurrentPage _
= Range("L1").Select
Selection.Copy
xlPasteValues

--------------------------------------------

Is there a way to build this? or is there an alternative route with similar output?


Many thanks in advance!
best
Tim

Hereby a sample workbook.

I tried working with the slicer but since the sheet consists of multiple datasets it's not quite as convenient as i'd be aiming for.

Also, Your quick response is being appreciated

Originally Posted by dflak

My morning meeting was canceled so I had time to work on this.

Here are a couple of modifications I made.

I made the data in Retouren data into an Excel table. Excel tables have too many advantages NOT to use them. I call it Table_Returns. I think if you rename it, everything that references it will change to the new name too.

I put in some named ranges:
Count_Returns ='Data Calculations'!$B$6
Count_Warranty ='Data Calculations'!$B$4
FIilter_RC =Dashboard2!$Q$6
Filter_RC =Dashboard2!$Q$6
Filter_RT =Dashboard2!$AB$7
Filter_VW =Dashboard2!$V$7
Filter_WC =Dashboard2!$V$37
Most_Critical =OFFSET(Dashboard2!$G$7,0,0,'Data Calculations'!$B$1-7,6)
Sel_Product =Dashboard2!$L$1

Most of these are static names. The only reason I use them is because VB doesn't know when you move things on a spreadsheet, but it does keep track of named ranges

There is one dynamic range: Most_Critical. As it relates to the current data, this range covers cells G7:L41 - since it is dynamic, it will grow and shrink with the data. I use this to help you select a part.

I have a sheet called Data Calculations - I try to get Excel to do as much work on the spreadsheet as it can and use VB only when I have to.
- Cell B1 is used to help calculate the dynamic range.
- Cell B3 tests to see if there is data in the warranty table - if there is none, I can't set the filter and give a warning.
- Cell B5 tests to see if there is data in the Returns table - if there is none, I can't set the filter and give a warning.
The Data Calculations sheet can be hidden.

I added one feature to your process. Instead of copying and pasting the product into cell L1, select any cell on the row with the product in the Most_Critical range. When you click on the Investigate Product button, the program will get the product name from column G on the same row you selected.

The program will then do a check to see if there is data for returns and warranty and set the filters. I am using a "short cut" here. If the data on which you want to filter is text data, then having VB "type" it directly into the filter will work. This will not work if the type of data isn't text data.

A word about translations: any label can be translated. If you look in the VB code, you can easily identify the msgboxes. You can change text data in them. Do NOT change the names of the named ranges. The code depends on them.

I appreciate you a lot! Will dive into it right away