How to make changes in multiple PivotTables by clicking one of them?

[feipezi]

Hi,
I have 4 PivotTables on the same sheet. None of them have PageFields so I cannot use CurrentPage property. I don't think we have CurrentRow or CurrentColumn property.
What I want is if I change the field value of one of the PTs (either RowFields or ColumnFields), the rest will follow.
Thanks in advance.

 

[SkipVought]

Hi,

"...change the field value of one of the PTs ..."

Do you mean SELECT a value in the field's selection box?

Did you try turning on your macro recorder?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[feipezi]

Yes. If I select a value 'ABC' in the RowFields of one of the PTs, the rest of the PTs are supposed to show 'ABC' in the same field.
I'll try macro recorder as you suggested and let you know what happens.
Thanks again.

 

[SkipVought]

Here are some things to consider...

1. you are working with PivotItems values and visible properties.

2. there must be at least ONE PivotItem visible at all times.

3. use the PivotTables collection and the PivotItems collection, similar to...

dim pvt as pivottable, pvi as pivotitem
for each pvt in activesheet.pivottables
  for each pvi in pvt.pivotfields("YourFieldName").pivotitems
    msgbox pvi.value
  next
next

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[feipezi]

Hi Skip,
It's a good idea. But when I apply the idea to my code, when I changed one of them, the rest wouldn't follow. Here is the code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim PFValue1, PFValue2 As Variant
Dim prd1, prd2, prd3, prd4 As PivotTable
Dim pf1, pf2 As PivotField
Dim strField1, strField2 As String

strField1 = "type"
'strField2 = "Organization Name S & N"

Set prd1 = Target
Set prd2 = ActiveSheet.PivotTables("prod2")
Set prd3 = ActiveSheet.PivotTables("prod3")
Set prd4 = ActiveSheet.PivotTables("prod4")
Set pf1 = prd1.PivotFields(strField1)
'Set pf2 = prd1.PivotFields(strField2)

'On Error Resume Next
If LCase(prd1.PivotFields(strField1).PivotItems.Value) <> LCase(PFValue1) Then
Application.EnableEvents = False
prd1.RefreshTable
PFValue1 = prd1.PivotFields(strField1).PivotItems.Value
prd2.PivotFields(strField1).PivotItems.Value = PFValue1
prd3.PivotFields(strField1).PivotItems.Value = PFValue1
prd4.PivotFields(strField1).PivotItems.Value = PFValue1
Application.EnableEvents = True
End If
End Sub

Thanks again.

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Sheet1.pivottables("PivotTable1").TableRange1) Is Nothing Then Macro1
    Application.EnableEvents = True
End Sub

Sub Macro1()
    Dim sItem As String, pvi As PivotItem, pvt As PivotTable
    
    For Each pvi In ActiveSheet.PivotTables("PivotTable1").PivotFields("name").PivotItems
        If pvi.Visible Then
            sItem = pvi.Value
        End If
    Next
    
    For Each pvt In ActiveSheet.PivotTables
        If pvt.Name <> "PivotTable1" Then
            For Each pvi In pvt.PivotFields("name").PivotItems
                pvi.Visible = True
            Next
            For Each pvi In pvt.PivotFields("name").PivotItems
                If pvi.Value <> sItem Then pvi.Visible = False
            Next
        End If
    Next
'
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}