I would like to make the following work across two pivot tables on two different worksheets. I can get this to work when the pivots are on the same worksheets but not on separate ones. Do I need to add an array?
Option Explicit
Private Sub Worksheet_Change _
(ByVal Target As Range)
Dim ws As Worksheet
Dim wsL As Worksheet
Dim Pt As PivotTable
Dim pf As PivotField
Dim pfD As PivotField
Dim rngType As Range
Dim rngTypeSel As Range
Dim rngNum As Range
Dim lType As Long
On Error GoTo errHandler
Set ws = ActiveSheet
Set wsL = Worksheets("Lists")
Set Pt = ws.PivotTables(1)
Set pf = Pt.PivotFields("TIN")
Set pfD = Pt.DataFields(1)
Set rngType = ws.Range("TypeSel")
Set rngTypeSel _
= wsL.Range("TypeValSel")
Set rngNum = ws.Range("NumSel")
Select Case Target.Address
Case rngType.Address, _
rngNum.Address
Application.ScreenUpdating = False
pf.ClearAllFilters
If rngNum.Value > 0 And _
rngType > 0 Then
pf.PivotFilters.Add _
Type:=rngTypeSel.Value, _
DataField:=pfD, _
Value1:=rngNum.Value
End If
End Select
exitHandler:
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Could not apply filter"
Resume exitHandler
End Sub
Option Explicit
Private Sub Worksheet_Change _
(ByVal Target As Range)
Dim ws As Worksheet
Dim wsL As Worksheet
Dim Pt As PivotTable
Dim pf As PivotField
Dim pfD As PivotField
Dim rngType As Range
Dim rngTypeSel As Range
Dim rngNum As Range
Dim lType As Long
On Error GoTo errHandler
Set ws = ActiveSheet
Set wsL = Worksheets("Lists")
Set Pt = ws.PivotTables(1)
Set pf = Pt.PivotFields("TIN")
Set pfD = Pt.DataFields(1)
Set rngType = ws.Range("TypeSel")
Set rngTypeSel _
= wsL.Range("TypeValSel")
Set rngNum = ws.Range("NumSel")
Select Case Target.Address
Case rngType.Address, _
rngNum.Address
Application.ScreenUpdating = False
pf.ClearAllFilters
If rngNum.Value > 0 And _
rngType > 0 Then
pf.PivotFilters.Add _
Type:=rngTypeSel.Value, _
DataField:=pfD, _
Value1:=rngNum.Value
End If
End Select
exitHandler:
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Could not apply filter"
Resume exitHandler
End Sub