Worksheet_ChangeByVal Target Update Code

[JADownie]

Hello -

I have the below code I wrote, which works fine now. However, I would like to adjust the code below in red to include PivotTable2 as well as PivotTable6. When I tried ("PivotTable2","PivotTable6") now I got an error...

Private Sub Worksheet_Change(ByVal Target As Range)
 
   On Error GoTo ErrorHandler
 
    Dim PvtTable As PivotTable, PvtItem As PivotItem
 
    If (Intersect(Target, Range("B1")) Is Nothing) Then
        Exit Sub
    End If
 
    'PivotTable to update
    Set PvtTable = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable2")
 
    'Clear all filters on the Job Number Field
    PvtTable.PivotFields("Job Number").ClearAllFilters
 
    'Turn off Application calculation and screen updating
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
  
    'Stop the PivotCache from recalculating until the selection change has been made
    PvtTable.ManualUpdate = True
 
    For Each PvtItem In PvtTable.PivotFields("Job Number").PivotItems
        'If the current PivotItem caption isn't the same as the selection in cell B1
        'Then set the Item to Visible=True, otherwise Visible=False and hide
        PvtItem.Visible = (PvtItem.Caption = CStr(Range("B1").Value))
    Next
 
ErrorHandler:
  
    'Turn on Application calculation and screen updating
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    'Turn the PivotCache calculation on again
    PvtTable.ManualUpdate = False
  
  
End Sub

 

[JADownie]

Also tried this now, which runs without error but PivotTable6 does not update only 2

'PivotTable to update
Set PvtTable = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable2")
Set PvtTable = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable6")

 

[RoryA]

Assuming you want to do the same thing to both pivot tables, you can refactor the processing code into a separate routine and call it twice passing each pivot table:

Private Sub Worksheet_Change(ByVal Target As Range)
 
   On Error GoTo ErrorHandler
 
    If (Intersect(Target, Range("B1")) Is Nothing) Then
        Exit Sub
    End If
 
    'Turn off Application calculation and screen updating
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .enableevents = false
    End With

   With ThisWorkbook.Worksheets("Summary")
      UpdatePivotTable .PivotTables("PivotTable2")
      UpdatePivotTable .PivotTables("PivotTable6")
   End With
 
ErrorHandler:
 
    'Turn on Application calculation and screen updating
    With Application
        .Enableevents = true
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
 
End Sub

Sub UpdatePivotTable(PvtTable as PivotTable)
    'Clear all filters on the Job Number Field
    PvtTable.PivotFields("Job Number").ClearAllFilters
  
    'Stop the PivotCache from recalculating until the selection change has been made
    PvtTable.ManualUpdate = True

    Dim PvtItem As PivotItem
    For Each PvtItem In PvtTable.PivotFields("Job Number").PivotItems
        'If the current PivotItem caption isn't the same as the selection in cell B1
        'Then set the Item to Visible=True, otherwise Visible=False and hide
        PvtItem.Visible = (PvtItem.Caption = CStr(Range("B1").Value))
    Next
    'Turn the PivotCache calculation on again
    PvtTable.ManualUpdate = False

End Sub

 

[JADownie]

Yes I do want to do the same thing to both pivot tables. Unfortunately when I tried your code above in my file now it still only updated PivotTable2

 

[RoryA]

I just amended the code to disable events while it's processing the pivot tables, so please try that. If it still doesn't work, that suggests it is erroring on the second pivot table for some reason.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Worksheet Change ByVal Target Code Update
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.