E
EZ
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.
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.