Refresh all pivot tables in another worksheet

[Dahlia]

Hye Excel Experts,

I found this awesome code to able to refresh all pivot tables at once when a criteria is selected in a validation list. However, I wish to have the validation list in Sheet1 but pivot tables in Sheet2. The codes seems to say all pivot tables in that same workbook should be refreshed once selection made but I see that only the pivot tables on same worksheet with the validation list were refreshed. Can someone help to correct the code for me, pls?

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
______________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[NARAYANK991]

Hi ,

The code is event based , and the event is change of any cell by user intervention ; this means that if your validation list is in Sheet1 , this code should be placed in the Sheet1 section in the Visual Basic editor.

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
              Set ws = ThisWorkbook.Worksheets("Sheet2")      ' Changed line of code
              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
            End If

            Application.EnableEvents = True
            Application.ScreenUpdating = True
End Sub

Narayan

 

[Dahlia]

Works like a charm as usual, NarayanK991!
Tq sooo much!!!