Hi.
I have 2 Pivot tables in a worksheet, both connected to their own query from an SQL database. What i am looking to do is filter both pivots using 1 slicer. After extensive research, i discovered this couldn't be done (even by amending the report connections). So my plan now is to add a slicer to each pivot and link the 2 using VBA, however my code isn't working:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ws As Worksheet
Dim pt As PivotTable
Dim slc1 As slicer
Dim slc2 As slicer
Dim slc1Name As String
Dim slc2Name As String
Dim slc1Item As String
Dim foundSlicer1 As Boolean
On Error Resume Next
Set ws = ThisWorkbook.Sheets("Query1")
slc1Name = "sumarised_name"
slc2Name = "sumarised_name 1"
Debug.Print "Sheet name: " & ws.Name
For Each pt In ws.PivotTables
foundSlicer1 = False
For Each slc1 In pt.Slicers
If slc1.Name = slc1Name Then
Debug.Print "First slicer found: " & slc1.Name
foundSlicer1 = True
slc1.ClearAllFilters
If slc1.HasSelectedItems Then
slc1Item = slc1.slicerCache.VisibleSlicerItemsList(1)
Debug.Print "Selected item from first slicer: " & slc1Item
For Each slc2 In pt.Slicers
If slc2.Name = slc2Name Then
Debug.Print "Second slicer found: " & slc2.Name
slc2.ClearAllFilters
slc2.SlicerItems(slc1Item).Selected = True
Debug.Print "Corresponding item in second slicer selected: " & slc1Item
Exit For
End If
Next slc2
Else
Debug.Print "No items selected in first slicer."
End If
Exit For
End If
Next slc1
If foundSlicer1 Then Exit For
Next pt
On Error GoTo 0
End Sub
Can anyone shed any light on why this isn't working, or any other suggestions?
