Excel Formula to Link Pivot Filter with a Cell

[shansakhi]

Dear All,
Is there a way to link a cell to Pivot Filter with Excel Formula.
I have gone through the VBA but its clearing all the Filters which is not suitable for me as I do have few filters which I do not want to clear.
Hence requesting your expertise for excel formula which can control Pivot Table.

Regards,
Shan

 

[RoryA]

A formula cannot control a pivot table. (It can affect what is shown but only after refreshing the pivot table somehow) What is the VBA you have now and what is the issue with it specifically?

 

[shansakhi]

Thank you for reply.
I have below VBA code which I took from this website. But in code I saw clear all filters line hence did not used that. I do not want to use clear filter option as it will clear all my filters set in pivot.

I have below set of data.
Sheet Name: Final (where I want to to type Value in a Cell which will be linked to pivot.
Sheet Name: Actuals (here two pivots are available with same data source)
Sheet Name: Budget (Here one pivot available with different data source)


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = Range("B2").Address Then Exit Sub
Dim PT As PivotTable
Dim ptItem As PivotItem
On Error Resume Next
For Each PT In Worksheets("Sheet1").PivotTables
With PT.PivotFields("MyReportField")
If .EnableMultiplePageItems = True Then
.ClearAllFilters
End If
Set ptItem = .PivotItems(Target.Value)
If Not ptItem Is Nothing Then
.CurrentPage = Target.Value
End If
End With
Next
End Sub
Regards
Shan

 

[RoryA]

That line only clears the filters on the specific field, not all the filters on the pivot table.

 

[shansakhi]

Thank you. Sorry for late reply.
The macro is working fine for me. I have two more issue with this.

1.When I am removing value from cell Pivot filters remains constant on the last filtered value. I want the filter to be opened when value from cell is removed.

2. I have tried using same macro for different Pivot fields of same pivot but its not working. Do I need some changes in this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = Range("S2").Address Then Exit Sub
Dim PT As PivotTable
Dim ptItem As PivotItem
On Error Resume Next
For Each PT In Worksheets("Actuals").PivotTables
With PT.PivotFields("Region")
If .EnableMultiplePageItems = True Then
.ClearAllFilters
End If
Set ptItem = .PivotItems(Target.Value)
If Not ptItem Is Nothing Then
.CurrentPage = Target.Value
End If
End With
Next
End Sub