Filter Excel pivot table using VBA

Field.CurrentPage only works for Filter fields (also called page fields).
If you want to filter a row/column field, you have to cycle through the individual items, like so:

Sub FilterPivotField(Field As PivotField, Value)    Application.ScreenUpdating = False    With Field        If .Orientation = xlPageField Then            .CurrentPage = Value        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then            Dim i As Long            On Error Resume Next ' Needed to avoid getting errors when manipulating PivotItems that were deleted from the data source.            ' Set first item to Visible to avoid getting no visible items while working            .PivotItems(1).Visible = True            For i = 2 To Field.PivotItems.Count                If .PivotItems(i).Name = Value Then _                    .PivotItems(i).Visible = True Else _                    .PivotItems(i).Visible = False            Next i            If .PivotItems(1).Name = Value Then _                .PivotItems(1).Visible = True Else _                .PivotItems(1).Visible = False        End If    End With    Application.ScreenUpdating = TrueEnd Sub

Then, you would just call:

FilterPivotField ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode"), "K123223"

Naturally, this gets slower the more there are individual different items in the field. You can also use SourceName instead of Name if that suits your needs better.

Configure the pivot table so that it is like this:

Your code can simply work on range("B1") now and the pivot table will be filtered to you required SavedFamilyCode

Sub FilterPivotTable()Application.ScreenUpdating = False    ActiveSheet.Range("B1") = "K123224"Application.ScreenUpdating = TrueEnd Sub

You could check this if you like. :)

Use this code if SavedFamilyCode is in the Report Filter:

 Sub FilterPivotTable()   Application.ScreenUpdating = False   ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True   ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").ClearAllFilters   ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").CurrentPage = _      "K123223"  ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False  Application.ScreenUpdating = True  End Sub

But if the SavedFamilyCode is in the Column or Row Labels use this code:

 Sub FilterPivotTable()     Application.ScreenUpdating = False     ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True      ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").ClearAllFilters      ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").PivotFilters. _    Add Type:=xlCaptionEquals, Value1:="K123223"  ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False  Application.ScreenUpdating = True  End Sub

Hope this helps you.