Code to link Pivot page fields to DV Lists
 

I know this is pretty easy, but i'm kind of new to VBA



I have found this code in Debra Dalgleish Site.

It's about linking Pivot table page field to a cell dropdown list.

I need it modified to do multiple page fields...

Codes between $$ $$ are my attempt.



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 = "Country"
$$ strField = "Location" $$
$$ strField = "Product" $$

or

$$ strField = ("Country","Location","Product") $$

or

$$ strField = MyRange (I have created a name range for B2:B4) $$



On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B2").Address Then
$$ If Target.Address = Range("B2:B4").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

*******************

I also tried to use the same code 3 times (changing the variables to
something like ws2, ws3...) but didn't work either... not sure if it's
because the worksheet change event is private or...



Thanks.