Filter a pivot table based on a cell value (OLAP)

Question

I'm trying to create a filter in a pivot table based on a cell value in Excel VBA. I have found this code below but it does not work. I have attached a screenshot containing the pivot table. I want to filter "M?nad" as "May" (which is the input in cell D69).

Sub FilterPageValue()
Dim pvFld As PivotField
Dim strFilter As String
Set pvFld = ActiveSheet.PivotTables("PivotTable7").PivotFields("M?nad")
strFilter = ActiveWorkbook.Sheets("220").Range("D69").Value
pvFld.CurrentPage = strFilter
End Sub

While doing this i reach a run-time error '1004': Application-defined or object-defined error. Name of pivot table are "PivotTable7". Name of sheet are "220". Input value is in cell D69. Note that the data comes from a external source (OLAP Cube) which I think is the main reason of this issue.

Please help! :-)

Pivot Table

I tried debug with:

Sub abc() Debug.Print 
ActiveSheet.PivotTables("PivotTable7").PivotFields(2).Name 
End Sub

Then I get: [Dim_Kalender].[Månad].[Månad]

Therefore i tried following code:

Sub FilterPageValue() 
Dim pvFld As PivotField 
Dim strFilter As String
Set pvFld = ActiveSheet.PivotTables("PivotTable7").PivotFields(" 
[Dim_Kalender].[Månad].[Månad]") 
strFilter = ActiveWorkbook.Sheets("220").Range("D69").Value 
pvFld.CurrentPage = strFilter 
End Sub

Now I am stuck on "pvFld.CurrentPage = strFilter". Same error as before.

It seems like it is this row: Set pvFld = ActiveSheet.PivotTables("PivotTable7").PivotFields("Månad") — pelusch1, Apr 18 '23 at 17:15
Try looping over the fields and `Debug.Print` each of the field names, to see if they're different from what you expect. — Tim Williams, Apr 18 '23 at 17:51
Not really sure what you mean here but it’s a text. “May” formatted as text — pelusch1, Apr 18 '23 at 18:54
Maybe look at: https://stackoverflow.com/questions/72135184/unable-to-set-the-currentpage-property-of-the-pivotfield-class-with-pivot-tabl Are you getting the exact same error message as before? — Tim Williams, Apr 18 '23 at 18:55
Now I have tried and I can see that it is the same part. It's bugging by "pvFld.CurrentPage = strFilter"/ " .CurrentPageName = PFilter" — pelusch1, Apr 18 '23 at 19:06
Try looping over `pvFld.Items` and check their names are what you expect. Sorry I don't have access to an OLAP table to check anything out. — Tim Williams, Apr 18 '23 at 20:05

Filter a pivot table based on a cell value (OLAP)

0 Answers0