Multiple Piviot Table Report Filter

[psamu]

I would appreciate if some one help me on this. I have two piviot table in same sheet that is sharing same data, but I want to use one piviot report filter to change other piviot table report filter. ( Linkin both report filter)
Thanks

 

[Marcelo Branco]

Hi,

Assuming both PVT, named PivotTable1 and PivotTable2, have a field Code, maybe something like this
(adjust the PVT-names and the field-name accordingly)

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim PVF1 As PivotField, PVF2 As PivotField
    Dim i As Long
    
    Set PVF1 = ActiveSheet.PivotTables("PivotTable1").PivotFields("Code")
    Set PVF2 = ActiveSheet.PivotTables("PivotTable2").PivotFields("Code")
    
    Application.EnableEvents = False
    
    For i = 1 To PVF2.PivotItems.Count
        PVF2.PivotItems(i).Visible = PVF1.PivotItems(i).Visible
    Next i
    
    Application.EnableEvents = True
End Sub

HTH

M.

 

[psamu]

Thanks. I tried, but it is giving an error. May be something I am doing wrong. I copied the code in sheet where both pivot tables are available. and changed the code to "project".

Report Field name is Project and Pivot table on PVT1 and Second one PVT2.

 

[Marcelo Branco]

Hi,

Did you check if the real names of the Pivot Tables are PivotTable1 and PivotTable2?

Click on any cell of the pivot table(s) and click on the tab Pivot Table Tools and take a look at the text-box on the left of the ribbon.

M.

 

[psamu]

Yes. I changed Pivot table name according to the code, but still getting debug yellow error on " PVF2.PivotItems(i).Visible = PVF1.PivotItems(i).Visible"



Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim PVF1 As PivotField, PVF2 As PivotField
Dim i As Long

Set PVF1 = ActiveSheet.PivotTables("PivotTable1").PivotFields("Project")
Set PVF2 = ActiveSheet.PivotTables("PivotTable2").PivotFields("Project")

Application.EnableEvents = False

For i = 1 To PVF2.PivotItems.Count
PVF2.PivotItems(i).Visible = PVF1.PivotItems(i).Visible
Next i

Application.EnableEvents = True
End Sub

 

[Marcelo Branco]

psamu,

The two PVT have the same number of items in the Project field? In other words, the same number of projects.

If one, for some reason, has more Projects than the other this can be the source of the problem.

I made a test with 2 PVT with the same number of items on the field Code (my case) and it worked fine.


M.

 

[psamu]

Yes, same number of items in the project field. Only Pivot values field are different. ( I mean some are not selected) I created two pivot table in one sheet because, I want a chart that shows less item than the detailed one. Actually, I want to create a graph in same sheet on the top shows a graph and bottom with data, but data need to be more than the items values than the graph shows, but report filter will be same for both. I tried to remove some items value from pivot graph, but it is not letting me. I think graph is automatic. Thanks for the help.