Macro to Link Cell value with Pivot Tables

[ShanShami]

Dear All,
I am very new to Excel Macros. I have a below code which take the value in "S2" and filter the "Region" pivot filter. I need to check a possibility of changing the code as per my requirement.

1. When Cell value is removed i.e. when it is kept as blank then the respective pivot field also should go back to all. Currently its remains filtered based on the last cell value.

2. I have tried to use same code for different Pivot fields of same pivot but its not giving any result.
e.g. Currently the Pivot Field "Region" is changing based on "S2" cell value.
I want to add more three Pivot fields and Cell reference to the same.

Pivot field "Region" .... Cell "S2"
Pivot field "Route" .... Cell "S3"
Pivot field "Sector" .... Cell "S4"
Pivot field "Cabin" .... Cell "S5"

But this will be an individual action that means I am going to use only one filter at one time. either Region or Route or Sector or Cabin.

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

I hope I am able to explain you properly.

Regards,
ShanShami

 

[NARAYANK991]

Hi ,

Please upload your complete workbook , with the raw data , the pivot table , and the code in it.

Narayan