I need to make the following code only apply to pivot tables in a specified worksheet. For example, changing the data validation is cell A3 will only update the pivot tables in sheet1
![]()
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 = "IP1" On Error Resume Next Application.EnableEvents = False Application.ScreenUpdating = False If Target.Address = Range("A3").Address Then For Each ws In ThisWorkbook.Worksheets 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











LinkBack URL
About LinkBacks

Register To Reply
Bookmarks