Pivot Table Filter based on Selected Cell

[dversloot1]

Hello,

I have created a spreadsheet that identifies subcategories that go beyond x std deviations from the mean. I currently have it set up so that if a cell is selected, the table group name, category, subcategory are found using vba.

I would like to then take those values, and filter a pivot table on another sheet. The code stops on .ClearAllfilters

Sub IdentifySpike()

Dim col As Integer
Dim rw As Integer
Dim GrpNm, Cat, SubCat As Variant

rw = Selection.Row

GrpNm = Cells(rw, 1)
Cat = Cells(rw, 2)
SubCat = Cells(rw, 3)

If MsgBox("Are you sure you want to drill down into: " & vbNewLine & Cat & " --> " & SubCat & vbNewLine & "at " & GrpNm & "?", vbYesNo, "Identify Spikes") = vbYes Then

Sheets("DrilledDown").Select

Dim pt As PivotTable
Set pt = Sheets("DrilledDown").PivotTables("PivotTable1")

pt.PivotFields("Group Name").ClearAllFilters
pt.PivotFields("Group Name").CurrentPage = GrpNm

Else
Exit Sub
End If

End Sub

I would like to repeat this filter method for Cat and SubCat as well.

Any ideas?

 

[dversloot1]

When I record a macro to perform the filter this is what I get:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Customer].[Group Name].[Group Name]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Customer].[Group Name].[Group Name]").CurrentPage = _
"[Customer].[Group Name].&[Amazon.Com CANADA]"

Does this help anyone for suggestions?