Change Specific Filter on All Pivot Tables in Workbook Based on Cell Value

[jski]

Good Day Mr. Excel team,

Trying to get this code to work and having a bit of trouble. I have two Pivot Tables in a workbook and need to change a filter on both based on changes made to a cell on another worksheet. I have a Workbook.SheetChange event set up on one sheet where the cell change will be made:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Not Intersect(Target, Application.Range(RegionRangeName).Offset(0, 1)) _
        Is Nothing Then
            UpdatePivotFieldFromRange _
            RegionRangeName, PivotFieldName, PivotTableName
    End If

End Sub

And more code in a module to run through each Pivot Table in the workbook to change the filter:

Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _
PivotTableName As String)


    Dim rng As Range
    Set rng = Application.Range(RangeName)
    
    Dim pt As PivotTable
    Dim Sheet As Worksheet
    For Each Sheet In Application.ActiveWorkbook.Worksheets
        On Error Resume Next
        Set pt = Sheet.PivotTables(PivotTableName)
    Next
    If pt Is Nothing Then GoTo Ex
    On Error GoTo Ex
    
    pt.ManualUpdate = True
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Dim Field As PivotField
    Set Field = pt.PivotFields(FieldName)
    Field.ClearAllFilters
    Field.EnableItemSelection = False
    SelectPivotItem Field, rng.Text
    pt.RefreshTable
    
Ex:
    pt.ManualUpdate = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub


Public Sub SelectPivotItem(Field As PivotField, ItemName As String)
    Dim Item As PivotItem
    For Each Item In Field.PivotItems
        Item.Visible = (Item.Caption = ItemName)
    Next
End Sub

I get a ByRef argument error in the Workbook.SheetChange event code at the 2nd 'RegionRangeName' bit. Tried a few things but can't seem to nail down the issue.


Thanks in advance.


jski