ubermensch
New Member
- Joined
- Jun 24, 2011
- Messages
- 6
Hi All
I received some help on another forum previously with some code to update a pivot table in one sheet based on the value specified in a pivot table in another sheet. This worked fine. -
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Account Number"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("C2").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
However, what I am finding is that when the value is changed in the "primary" pivot table, the value is added in the secondary pivot table. For example, if I select customer 1234 in the "primary" table, and the secondary table is currently set to customer 5678, customer 1234 gets selected in the secondary table in addition to 5678 instead of replacing 5678.
I think I need to add to the above code to somehow clear the existing value from the seconday pivot before updating with the new value from the primary table. Any help would be appreciated.
I received some help on another forum previously with some code to update a pivot table in one sheet based on the value specified in a pivot table in another sheet. This worked fine. -
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Account Number"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("C2").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
However, what I am finding is that when the value is changed in the "primary" pivot table, the value is added in the secondary pivot table. For example, if I select customer 1234 in the "primary" table, and the secondary table is currently set to customer 5678, customer 1234 gets selected in the secondary table in addition to 5678 instead of replacing 5678.
I think I need to add to the above code to somehow clear the existing value from the seconday pivot before updating with the new value from the primary table. Any help would be appreciated.