Vba Pivot FIlter and Slicer

Sometime happen that we want to filter in automatic way a pivot table without using filter o slicer. In this situation we can use VBA to do this kind of operation.

Pivot Filter

When you want to use a vba script to filter a pivot table you have to insert in the pivot table the filter that you want to use. After that you have to create this script:

ActiveSheet.PivotTables(“TABLE NAME“).PivotFields( _
MODEL TABLE FILTER“).VisibleItemsList = Array(“”)

ActiveSheet.PivotTables(“TABLE NAME“).PivotFields( _
MODEL TABLE FILTER“).VisibleItemsList = Array( _
MODEL TABLE FILTER + FILTER OBJECT“)

To use this script correctly we have to be more clear. The first Function clear all the previous selection on specific filter. For example if our pivot table name is “TABELLA1” and the attribute filter is the “CustomerName” the correct script is this :

ActiveSheet.PivotTables(“TABELLA1”).PivotFields( _
“[CustomerTable].[CustomerName].[CustomerName]”).VisibleItemsList = Array(“”)

[CustomerTable].[CustomerName].[CustomerName] depends on your model structure.

The second script is used to filter for a specific value, for example if we wanto to filter using the customer name “Microsoft Corporation” we have to write like this:

ActiveSheet.PivotTables(“TABELLA1”).PivotFields( _
“[CustomerTable].[CustomerName].[CustomerName]”).VisibleItemsList = Array( _
“[CustomerTable].[CustomerName].&[Microsoft Corporation]”)

if we want to use a variable where is stored customer name (ex. NCUST) we have to write script like this:

ActiveSheet.PivotTables(“TABELLA1”).PivotFields( _
“[CustomerTable].[CustomerName].[CustomerName]”).VisibleItemsList = Array( _
“[CustomerTable].[CustomerName].&[” & NCUST &”]”)

Slicer

Control a slicer with VBA is more simple than a normal filter. we have two different script:

ActiveWorkbook.SlicerCaches(“SLICERNAME“).ClearManualFilter

ActiveWorkbook.SlicerCaches(“SLICERNAME“).VisibleSlicerItemsList _
= Array( _
MODEL TABLE FILTER“)

To know Slicer Name you have to select the slicer, right click on it , select options and find Name to use in the formula. After that, if for example the name is Filter_customerName, we change the script like this:

ActiveWorkbook.SlicerCaches(“Filter_CustomerName”).ClearManualFilter

If we wanto to filter to a specific customer, like before we have to write the model structure:

ActiveWorkbook.SlicerCaches(“Filter_CustomerName”).VisibleSlicerItemsList _
= Array( _
“[CustomerTable].[CustomerName].&[Microsoft Corporation]”)

If we want to use a variable:

ActiveWorkbook.SlicerCaches(“Filter_CustomerName”).VisibleSlicerItemsList _
= Array( _
“[CustomerTable].[CustomerName].&[” & NCUST &”]”)

 

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...