Filtering multiple pivots on same sheet from a Referenced Cell value

[Kelew]

Hi

So I am in the process of creating a dashboard with multiple data sources. I have a slicer for one dataset using a variable which is consistent throughout my datasets (LSOA Code). Annoyingly i don't have the latest version of excel where you can have a slicer across multiple datasets. My workplace is only on 2010 version .

Basically i have a slicer already in place for the user to select the LSOA code they wish to visualise on the dashboard. For the next dataset i have managed to link the first pivot using a reference cell and this VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("C1637:C1638")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("pivots").PivotTables("MosaicGroup")
Set Field = pt.PivotFields("LSOANM11")
NewCat = Worksheets("dwelling fire pivots").Range("C1637").Value


With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

and it works amazingly! but when i repeat this code to link the second pivot table to the same reference cell, it didn't work. No error appears - just nothing happens. I even tried creating a new reference cell... (see second vba code below)

Private Sub Worksheet_SelectionChange2(ByVal Target As Range)

If Intersect(Target, Range("C1662:C1663")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("pivots").PivotTables("Type1")
Set Field = pt.PivotFields("LSOANM11")
NewCat = Worksheets("dwelling fire pivots").Range("C1662").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

My VBA is rusty and barely used, so i'm re-learning it all again. Am i doing something wrong? I think i'm on the right track but perhaps missing a step to add in the second pivot being filtered.

Any help would be greatly appreciated.

Thanks

K

 

[jorismoerings]

Hi and welcome to MrExcel,

As to what i can see from the code (note: please read forum rules regarding "How to post code") there's a few things i can't match with your description:
The question asked is to change all pivot tables on the worksheet with 1 change.

using a variable which is consistent throughout my datasets

while the 2 codes don't refer to the same cell being changed,

The first one refers to

Target, Range("C1637:C1638"))

and the second refers to

Target, Range("C1662:C1663"))

If the description is correct than the 2 target ranges aren't or ...

Your second code refers to

Private Sub Worksheet_SelectionChange2

However this type of VBA coding uses excel events and unfortunately there's no event named "Worksheet_SelectionChange2" so this code will never be triggered.

Just as a tip: if both pivot tables are on the same worksheet and are related to the same variable, you need just one code to change them both.

Let us know what you want.

Hope this helps.