Select criteria in another worksheet refreshes pivot tables in other worksheets

[dellzy]

Hye Excel Experts,

This code I found seems to be really awesome. However, I couldn't get it to refresh the pivot tables automatically when I park the selection list in another worksheet. It only works when the list in the same worksheet as the pivot tables. Please help to correct the code below to meet my objective of having the list in Sheet1 but refreshed pivot tables in Sheet2.

Option Explicit


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 = "Mth/Yr"


On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False


    If Target.Address = Range("Month").Address Then
        
'        For Each ws In ThisWorkbook.Worksheets
        Set ws = Me
            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

Thank you in advance.
DZ