Creating Macros with Cube Connections - Trying to use Combo Box with Cube generated PT

[charlesutton]

Hi Forum,

Trying to create a combo box that auto filters a cube generated pivot table. My code is provided below, but breaks at line 4. For some reason, the PT filter can't read the cell value in A54. Any help is greatly appreciated.

Sub StateChangeTest()

ActiveSheet.PivotTables("StateDropsPivot").PivotFields( _
"[Location].[State Name].[State Name]").ClearAllFilters
ActiveSheet.PivotTables("StateDropsPivot").PivotFields( _ <-- Breaks Here
"[Location].[State Name].[State Name]").CurrentPage = _
Worksheets("QuitDataState").Range("A54").Value

End Sub

 

[Jerry Sullivan]

Hi charlesutton,

If the value in A54 is just a state name or code (say "Texas"), your current code tries to set the current page to "Texas"

For a cube source, you'll need to use a member expression like "[Location].[State Name].[All].[Texas]"

The exact expression will depend on the hierarchy of the cube. If you record a macro while manually setting that Report Filter, you can get the pattern.

If the pattern matched the example above you could build the expression like this....

sState = Worksheets("QuitDataState").Range("A54").Value

ActiveSheet.PivotTables("StateDropsPivot").PivotFields( _ 
 "[Location].[State Name].[State Name]").CurrentPage = _
 "[Location].[State Name].[All].[" & sState & "]"