Linked Pivot Table doesn't refresh

[03856me]

I have two pivot tables on the same worksheet, tied to different tables, but both have a "date" column. When I change the date in either tables Report Filter this code does change the page field in the other one, but the data does not refresh. My primary table is PivotTable2, once I can get this working I would hide that report filter in PivotTable1. Can someone help me out here.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
    PivotMacro
    PivotMacro1
Application.ScreenUpdating = True
End Sub
'==========================================================
Sub PivotMacro()
    Dim PT As PivotTable
        Set PT = ActiveSheet.PivotTables("PivotTable1")
        PT.RefreshTable
End Sub
'==========================================================
Sub PivotMacro1()
    Dim PT As PivotTable
        Set PT = ActiveSheet.PivotTables("PivotTable2")
        PT.RefreshTable
End Sub
'==========================================================
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim PT As PivotTable
  Dim NewPage
    If Intersect(Target, Range("D10")) Is Nothing And _
    Intersect(Target, Range("D30")) Is Nothing Then
    Exit Sub
  Else
    If Not Intersect(Target, Range("D10")) Is Nothing Then
      NewPage = Range("D10").Value
    End If
    If Not Intersect(Target, Range("D30")) Is Nothing Then
      NewPage = Range("D30").Value
    End If
    Application.EnableEvents = False
    For Each PT In ActiveSheet.PivotTables
      PT.PageFields("date").CurrentPage = NewPage
      PT.ManualUpdate = False
      PT.ManualUpdate = True
    Next PT
    Application.EnableEvents = True
  End If
End Sub

 

[Jerry Sullivan]

When I change the date in either tables Report Filter this code does change the page field in the other one, but the data does not refresh.

While it appears that your other PivotTable is changing the current page but not refreshing; more likely what is happening is that the current page is being renamed to match the .Value property of the other PivotTable's current page.

This happens when VBA tries to Set a new current page, but can't find a match - it renames the pivotitem that is the existing current page.
If you were actually setting a different report filter as you intended, you shouldn't need to refresh the PivotTable to see the new filter take effect.

Working with date values can be challenging because in some cases Excel uses the date's .Value property and in other cases it uses the Text property.

You can prevent the unintentional renaming of PivotItems by adding a .ClearAllFilters statement.

You can try to address the date formatting compatibility by using the .Text property instead of the .Value property.

Try this modified version.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PT As PivotTable
    Dim NewPage As String

    
    If Intersect(Target, Range("D10")) Is Nothing And _
        Intersect(Target, Range("D30")) Is Nothing Then Exit Sub

        
    If Not Intersect(Target, Range("D10")) Is Nothing Then
        NewPage = Range("D10").Text
    Else
        NewPage = Range("D30").Text
    End If

    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    For Each PT In ActiveSheet.PivotTables
        PT.ManualUpdate = True
        With PT.PageFields("date")
            .ClearAllFilters
            .CurrentPage = NewPage
        End With
        PT.ManualUpdate = False
    Next PT

CleanUp:
    If Not PT Is Nothing Then PT.ManualUpdate = False
    Application.EnableEvents = True

End Sub

 

[03856me]

That makes total sense, thank you for explaining.

THIS WORKED PERFECTLY........I really appreciate your help.