Pivot table link with drop down

[MohitMathur1234]

Hi I have 2 drop down and 2 pivot tables

I want Pivot_1 link with dropdown_1 and Pivot_2 link with dropdown_2 but Same field "Year". for more I have attached sheet for reference.

I have tried the below code but it is changing both pivottables with same dropdown.

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 = "Year"

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

If Target.Address = Sheets("Main").Range("B3").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

 

[Chihiro]

Why are you looping through each sheet and each pivot table?

From your post, single drop-down is tied to single Pivot.
Instead of loop, use 2 set of code for each drop-down.

 

[MohitMathur1234]

Hi, In my dashboard. I have two drop down. where i need to compare 2015 vs 2016.. please help ..

 

[Chihiro]

As I have stated, looping is the cause of your problem. Also you are checking for single cell change only. Just write specific code for each cell.

However, why use code when you are not familiar with it? It's far easier to maintain set up described in link below. Linking PivotTable with another PivotTable via slicer.

http://chandoo.org/forum/threads/top-10-with-multiple-criteria.31216/#post-185928

No coding needed.