Hi,
I am self-teaching VBA for Excel and trying to achieve the following:
1) Select a product in Pivot table 1 -> field 'From Name'
The code shoud then help to:
2) Pivot table 2 will select the same product in field 'To Name' (same data source)
3) Pivot table 3 will select the same product in field 'From Name' (different data source)
I found the code by Robert Rosenberg where he seems to explain how to do this. I downloaded the file (see attached) and run the code and although there are not error messages popping up, the code doesnt do what is supposed to (or at least how i understand the code)
I have been going through multiple cycles of trial and error to have the code working, and i managed to get it working using the same source and the same field. however, i need to go a step further, that is using the same data source, diff field from main pivot table, and using diff data source, same field as the main pivot table.
Could anyone help me to make this code work how i listed above?
Thanks in advance
------------------------------------------------------
The code is (file attached):
[vba]Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim wsOther As Worksheet
Dim pt As PivotTable
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable
Dim pi As PivotItem
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim strField4 As String
strField1 = "Item"
strField2 = "Region"
strField3 = "Product"
strField4 = "District"
Set wsOther = Sheets("Other Pivots")
Set pt = Target
Set pt1 = wsOther.PivotTables("PT1")
Set pt2 = wsOther.PivotTables("PT2")
Set pt3 = wsOther.PivotTables("PT3")
On Error Resume Next
If LCase(pt.PivotFields(strField1).CurrentPage) <> LCase(mvPivotPageValue1) Then
'The PageField1 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue1 = pt.PivotFields(strField1).CurrentPage
With pt1.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt2.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt3.PageFields(strField3)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Application.EnableEvents = True
End If
If LCase(pt.PivotFields(strField2).CurrentPage) <> LCase(mvPivotPageValue2) Then
'The PageField2 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue2 = pt.PivotFields(strField2).CurrentPage
With pt1.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt2.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt3.PageFields(strField4)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Application.EnableEvents = True
End If
End Sub [/vba]
Reply With Quote
