Update Page field From Another PT - VB help

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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Man, things move so fast on this forum !!. Just hoping someone has some insight in to the code problem above.

Cheers.
 
Upvote 0
Well, for anyone that's interested I fixed this my self by adding EnableMultiplePageItems = False into the code. It now looks like this -

Code:
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
            EnableMultiplePageItems = False
                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
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top