[SOLVED] Writing VBA to filter through OLAP Pivot Table Fields

Writing VBA to filter through OLAP Pivot Table Fields

I'm new to VBA. I have an excel file with a pivot table created by OLAP Cube. I try to create a variable that filters a specific column within the Pivot Table.

I used Record Macro and this is what I've got:

Sub Macro1()

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Item].[ItemByProducer].[ProducerName]").VisibleItemsList = Array( _
        "[Item].[ItemByProducer].[ProducerName].&[the name of the producer]")

End Sub

The macro works well for that specific producer, but I want to create a variable of type string in which I can filter by other producers also.

How can I achieve that?

Solution

Put the variables in an array - producers = Array("ProducerA", "ProducerB", "ProducerC")

Then loop over them with the code below. Just make sure to change the name of "NameOfWorksheet" to the sheet you are using. ActiveSheet is not a good idea - How to avoid using Select in Excel VBA:

Sub TestMe()

    Dim producers As Variant
    producers = Array("ProducerA", "ProducerB", "ProducerC")
    Dim producer As Variant
    
    For Each producer In producers
        ThisWorkbook.Worksheets("NameOfWorksheet").PivotTables("PivotTable1").PivotFields( _
            "[Item].[ItemByProducer].[ProducerName]").VisibleItemsList = Array( _
                "[Item].[ItemByProducer].[ProducerName].&[" & producer & "]")
    Next    

End Sub