Creating a button to select from a Pivot table filter

[chriswhincup]

Hi,
I have a filter for a pivot table and the list is essentially 2000 sales people. I only ever need to be able to select one of 6 names. I don't want to use a slicer as that shows all 2000 names and scrolling makes it difficult. The filter then brings up a slicer with the stores that person looks after. Selecting the store then updates the data from 9 other linked pivot tables into a dashboard.

I thought I could use a button and assign a recorded macro to it that just chooses the name from the filter but its not working properly. It works fine when its recording and does exactly what its meant to do. Can anyone spot anything glaringly obvious with the code? The debug seems to highlight the line with CurrentPage and the line below on it. Alternatively if there is a better way to do it?

Sub HelenRouse()
'
' HelenRouse Macro
'

'
ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"[Sales Person].[Sales Person].[Sales Person]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"[Sales Person].[Sales Person].[Sales Person]").CurrentPage = _
"[Sales Person].[Sales Person].&[HELEN ROUSE]"
End Sub

 

[Micron]

Don't know much about pivot tables but do know a thing or two about concatenation in code. You are including the ampersand as a literal character in the reference and I doubt that anything is named "&[HELEN ROUSE]". Try "[Sales Person].[Sales Person]." & "[HELEN ROUSE]"
If the first line in your code doesn't raise an error then I guess including brackets ( [ ) in your references is OK, but as I noted, pivot stuff isn't my thing. It may also be that there will be a missing double quote or two as a result of breaking it up like that.