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