I'm trying to use VBA with PowerPivot, and I'd like to change the slicer field by using a variable. The code below was from recording a macro and works fine when the client's name is explicit as in the code below. When I try to replace Smith in the code with a string variable, I get a "Run-time error '1004': The item could not be found in the OLAP Cube." Using a variable for a filter works fine with a regular pivot table, but I must be missing something with PowerPivot. Any help would be greatly appreciated!
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Holdings].[Client].[Client]").CurrentPageName = _
"[Holdings].[Client].&[Smith]"
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Holdings].[Client].[Client]").CurrentPageName = _
"[Holdings].[Client].&[Smith]"