VBA - Filter Pivot Based on Cell Reference - Check For No Values

I have the code below that auto filters a pivot table, "pvtTwo", based on a cell reference.  The cell references another pivot table, ("pvtOne").  So basically selecting a value to filter by in pivot table 1 auto filters for the same value in pivot table 2.
I'm noticing that when the value to filter by in pivot table 1 is not available in the data source of pivot table 2, it writes over the previously selected value in pivot table 2 that was showing.  Is there any way I can amend the code below to not re-label the value in Pivot 2?
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    On Error Resume Next
    Application.EnableEvents = False
    Me.PivotTables("pvtTwo").PivotFields ("State").CurrentPage = Range("AI7").Value
    Application.EnableEvents = True
End Sub

Open in new window

error_proneAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Try this update to your code, to validate existence before update.

Thomas

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    On Error Resume Next
    Application.EnableEvents = False
    Dim pt As PivotItem
    For Each pt In Me.PivotTables("pvtTwo").PivotFields("Fruit").PivotItems
        If pt.Name = Range("B8").Value Then
            Me.PivotTables("pvtTwo").PivotFields("Fruit").CurrentPage = Range("B8").Value
            GoTo endline:
        End If
    Next pt
    MsgBox "No matching data"
endline:
    Application.EnableEvents = True
End Sub

Open in new window

0
 
alainbrydenCommented:
Perhaps you could upload a sample file that demonstrates your problem.
0
 
error_proneAuthor Commented:
Here's a sample.  Notice that when you select "Orange" from the pivot on the left, (One), then the other pivot correctly also filters to Orange, (with 120 units).  But if you select "Banana" from pivot One, it renames Orange in pivot Two to Banana, (because Banana does not exist in Two).  I want it to show an empty pivot if the value is not available, (or something like that).  But it should not interfere with the ability to filter in Pivot One.
TEST.xlsm
0
 
error_proneAuthor Commented:
Perfect, thank you!!
0
 
nutschCommented:
Glad to help, thanks for the grade.

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.