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

[jski]

Good Day Chandoo 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 type mismatch 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

 

[Chihiro]

My guess. You'd need the code in Worksheet_Change event and not at Workbook level.

Open VBA module and locate the sheet where the code should be fired from. Right click and show code and locate Worksheet_Change event and paste in the body of the code.

 

[jski]

Yes, that is where I have it.

 

[Chihiro]

Hmm? But your code says otherwise...
"Private Sub Workbook_SheetChange(...)" This indicates that the code is in ThisWorkbook module.

If it's in worksheet and based on cell change...
It should start with following...
"Private Sub Worksheet_Change(ByVal Target As Range)"

 

[jski]

Ok thanks. But still receiving same error.

 

[Chihiro]

Upload sample workbook with dummy data for further help.

 

[jski]

Thanks for taking a look Chihiro.

 

[Chihiro]

There was few issues with your set up.

1. Both Pivots had same name. Changed PivotTable names to reflect what it is.
I.E. "ptActual" & "ptBudget".

2. Named range is actually "RegionFilterRange" not "RegionRangeName". I think you were using variables which were not defined in the code. That will cause the error you mentioned.

3. Also you'd need literal string for "PivotFieldName" & "PivotTableName"

See modified code below and also attached workbook with modifications.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Target, Range("E1")) _
        Is Nothing Then
            UpdatePivotFieldFromRange _
            "RegionFilterRange", "Market", "ptActual"
            UpdatePivotFieldFromRange _
            "RegionFilterRange", "Market", "ptBudget"
    End If

End Sub

 

[jski]

Thanks Chihiro. Did the macro fire and work for you? Not seeing the pivots change when E1 changes.

 

[Chihiro]

Yep. Worked fine for me.

Edit: Issue is that some items are not present in PivotItem, within PivotField "Market" (Ex. "Michigan Region").

 

[jski]

I see. That would be because they are the result of a multiple filter. For example, Michigan Region would be SE Michigan, Saginaw, Flint, and Mid-Michigan all selected in the same filter. I'll need to noodle on that a bit to try to figure out how to have this accommodate multiple filters.

Chihiro--thanks much. This was not only very helpful but instructive as well. I'll get a lot of traction and use of this code and variations thereof in some of my other work efforts.