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
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