I have pivot table based of an OLAP cube, and I'd like to filter one of the fields based on the value of another cell. The filter works when I use the ID number, but not when I use the name, so I wonder if I have something wrong in my syntax.
This works:
ActiveSheet.PivotTables("Sales").PivotFields("[Buying].[MCH].[MCH3]"). _
CurrentPageName = "[Buying].[MCH].[MCH3].&[69]"
But this doesn't
ActiveSheet.PivotTables("Sales").PivotFields("[Buying].[MCH].[MCH3]"). _
CurrentPageName = "[Buying].[MCH].[MCH3].[L Womenswear]"
Even though the ID for L Womenswear is 69. What I'd like is some version of this
ActiveSheet.PivotTables("Sales").PivotFields("[Buying].[MCH].[MCH3]"). _
CurrentPageName = "[Buying].[MCH].[MCH3].[X]"
Where X is a cell where I could write L Womenswear, or another department. I can get it to work with the ids if necessary, but it would simplify everything if I could use the names.