Changing Pivot table filters based on a value in another cell

[Dokat]

Hi,

I am trying to change pivot table geography field based on the selection in cell b4 in Summary tab. Its a drop down table. I am using below code however it is not working.

Can someone please help.

Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _PivotTableName As String)

Dim rng As Range    Set rng = Application.Sheets("Summary").Range(B4)        Dim pt As PivotTable    Dim Sheet As Worksheet    For Each Sheet In Application.ActiveWorkbook.Worksheets        On Error Resume Next        Set pt = ThisWorkbook.Sheets("Source Data").PivotTables(PivotTable1)    Next    If pt Is Nothing Then GoTo Ex    On Error GoTo Ex        pt.ManualUpdate = True    Application.EnableEvents = False    Application.ScreenUpdating = False        Dim Field As PivotField    Set Field = pt.PivotFields(Geography)    Field.ClearAllFilters    Field.EnableItemSelection = False    SelectPivotItem Field, rng.Text    pt.RefreshTableEx:    pt.ManualUpdate = False    Application.EnableEvents = True    Application.ScreenUpdating = True    End Sub

Public Sub SelectPivotItem(Field As PivotField, ItemName As String)    Dim Item As PivotItem    For Each Item In Field.PivotItems        Item.Visible = (Item.Caption = ItemName)    NextEnd Sub