Writing VBA to filter through OLAP Pivot Table Fields

Question

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?

You can concatenate the string with `&`: `"[Item].[ItemByProducer].[ProducerName].&[" & yourvariable & "]"`. — BigBen, Nov 13 '20 at 15:11
Actually this seemed to do the trick Sub Macro1() ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "[Item].[ItemByProducer].[ProducerName]").VisibleItemsList = Array( _ "[Item].[ItemByProducer].[ProducerName].&["& stringvariable &"]") End Sub — Sbeastan, Nov 13 '20 at 15:31

score 1 · Accepted Answer · answered Nov 13 '20 at 15:30

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

Writing VBA to filter through OLAP Pivot Table Fields

1 Answers1