Sometime happens that you need to filter in automatic way a table or a “slicer” using data written in a particular cell without select it with classical manual selection.

We start from a real Situation. I have created a simple table:

In this table we have a column with the customer name, a column with product category, the scenario of analysis, the version and a value. all the values are inserted manually. The structure is a table called “Values”. The table is imported in powerpivot as linked table ( it’s the same if import in sql and afetr in a model)

In other page i have a pivot table very simple, i have Version and scenario as attribute, value as measure, product category as “Slicer” and Customer as table FIlter:

First of all we have to save the information that we want to pass to filter. It a very simple code of vba. in the first page i have add a value in a cell that is the row number that i want to use to filter data.

First collect you data:

Sub useFilter()
Dim nrow As Number
Dim Customer As String
Dim Product As String
Dim Scenario As String

nrow = Cells(8, 2)
nrow = nrow + 1
Customer = Cells(1, nrow)
Product = Cells(2, nrow)
Scenario = Cells(3, nrow)
End Sub

I create some variables that takes value from specific cell, value nrow contains the value in cell H3, we add +1 because our table have header. and we use nrow to find and select our value.

Now we have to pass this infot to other sheet. First of all change page to the pivot table:7

Sheets(“Foglio2”).Select

After that we have to call filter and slicer, for the slicer is very simple, to clear all filter use this:

 ActiveWorkbook.SlicerCaches(“FiltroDati_Gerarchia_Cliente1“).ClearManualFilter

where “FiltroDati_Gerarchia_Cliente1” is the nanme of slicer that you find in his “slicer option”. to insert data:

ActiveWorkbook.SlicerCaches(“FiltroDati_Gerarchia_Cliente1”). _
VisibleSlicerItemsList = Array( _
“[Cliente].[Gerarchia Cliente].[Cliente fatturazione].&[” & Cliente & “]”)

You have to pass to Slicer:

• The name of slicer, in ex FiltroDati_Gerarchia_Cliente1
• The complete structure of your attribute, table.colum.&attirbute and substitute attribute with “& Our Variable & “

For filter table change a little the structure of vba, if you want to reset all:

ActiveSheet.PivotTables(“Tabella_pivot1”).PivotFields( _
“[Prodotto].[Product category].[Product category]”).VisibleItemsList = Array(“”)

Where “Tabella_pivot1” is the name of your pivot table where are the value that you want to filter. and the complete structure of the variable that you want to clear, in our example we clear filter from column Product category of Prodotto Table.

To insert a value as a filter:

ActiveSheet.PivotTables(“Tabella_pivot1”).PivotFields( _
“[Product category].[Product category].[Product category]”).VisibleItemsList = Array( _
“[Prodotto].[Product category].&[” & Funzione & “]”)

Where “Tabella_pivot1” is the name of your pivot table where are the value that you want to filter and the complete structure of the variable that you want to filter. I substitute attribute with “& Our Variable & ”

This is a complete example of this vba fuction:

Sub RefreshNetNetPrevisionale()
Dim Cliente As String
Dim Funzione As String
Dim Scenario As String

Cliente = Cells(10, 3)
Funzione = Cells(11, 3)
Scenario = Cells(13, 3)
ActiveWorkbook.Connections(“LinkedTable_ NetNet_Previsionale”).Refresh

Sheets(“CE Previsionale”).Select

ActiveWorkbook.SlicerCaches(“FiltroDati_Gerarchia_Cliente1”).ClearManualFilter
ActiveWorkbook.SlicerCaches(“FiltroDati_Scenario”).ClearManualFilter
ActiveWorkbook.SlicerCaches(“FiltroDati_Versione1”).ClearManualFilter

‘ Cliente di fatturazione

ActiveWorkbook.SlicerCaches(“FiltroDati_Gerarchia_Cliente1”). _
VisibleSlicerItemsList = Array( _
“[Cliente].[Gerarchia Cliente].[Cliente fatturazione].&[” & Cliente & “]”)

‘ funzione

ActiveSheet.PivotTables(“Tabella_pivot1”).PivotFields( _
“[Prodotto].[Funzione].[Funzione]”).VisibleItemsList = Array( _
“[Prodotto].[Funzione].&[” & Funzione & “]”)

‘scenario

ActiveWorkbook.SlicerCaches(“FiltroDati_Scenario”).VisibleSlicerItemsList = _
Array( _
“[Scenario].[Scenario].&[” & Scenario & “]”)
End Sub