CODEWITHSUNDEEP
excelvbapivot-table
Fab
Fab

Reputation: 49

CubeField? Setting filter values on pivot table

I have an excel file with several Macros. In this file there is a pivot table with a column called "Customer", which contains several elements (thousands). What i want to know is, is there any efficient way to set up the filter of this column to specific values?

The reason why i am asking is because the spread sheet will ask the user to select some customers, which are stored in a array, I want to take the values in this array and filter the pivot table accordingly.

I was able to do this by looping the pivot table through all the items, making them visible = false, and then making those that were selected, visible = true. However, the issue with this approach is that it takes so much time to process.

I have seen some examples in the web of people using the CubeField option, however, I am unsure how to use it? The syntax is a bit complex, I have tried the following and it gives me an error:

Worksheets("Analysis").PivotTables("PivotTable1").PivotFields("Supplier_Code").ClearAllFilters
Worksheets("Analysis").PivotTables("PivotTable1").PivotFields("Supplier_Code").CubeField.EnableMultiplePageItems = True
Set pt = Worksheets("Analysis").PivotTables("PivotTable1")
With PT
    pt.PivotFields("[PivotTable1].[Supplier_Code]").VisibleItemsList = Array("[PivotTable1].[Supplier_Code].&[106214]", "[PivotTable1].[Supplier_Code].&[101160]") ' ===>

Here I am not using the array values, just trying to set the customers directly, 1 supplier code is 106214 and the other is 101160.

Any help to get me to the right direction will be highly appreciated. Also, please note that I am not looking to set the filter to 1 item only, I am able to do this already without any issues, the issue here is to select multiple values at once, without looping though all the possible values that could be selected.

Upvotes: 2

Views: 2114

Answers (1)

ExactaBox
ExactaBox

Reputation: 3395

When your PivotTable is selected, in the top menu, go to "PivotTable Tools," then "Options", then pick "Insert Slicer" from the Ribbon. This feature does what you want -- let's you easily select filters (one at a time or multiple), and see the different results of the PivotTable.

Upvotes: 1

Related Questions