Excel VBA loop slicer from selected item

[Coberg]

Hi all,

I'm working on a rather large template file that generates graphs and tables and calculated values from several data sets.
I have performed several steps to do so, and they all work:

1. Attach several datasources to excel (mostly existing excel tables)
2. Make pivot tables and slicers
3. Synchronise the slicers throughout the different data sources
4. Generate needed graphs, tables and calculations for large number of reports to be generated
5. Loop through slicer ietms to update tables and graphs
6. make copy of word template, polulate it with the graphs, tables and calculations
7. save the file under specific name.

Now i'm stuck on two issues:

1. Loop not through all slicer items but only from 1 selected sliceritem to end

So, for instance i have a pivottable with a slicer which contains 10 sliceritems
I now loop from sliceritem i=1 to slicercache.slicerietms.count, so through ALL the items
This works and it loops throught the 10 sliceritems.

I now want to be able to select a random item, lets say from the 3th sliceritem and loop till the last item.
Current code below:

For i = 1 To sc1.SlicerItems.Count ---> so the i =1 must change to the nr of the selected sliceritem

sc1.SlicerItems(i).Selected = True
If i <> 1 Then sc1.SlicerItems(i - 1).Selected = False

etc





2. Synchronise slicers through data sets with not 100% matching slicer items

In this example i have 2 datasets, with pivottables ans slicers.

First dataset has 6 names and 6 surnames in it (and so does the pivottable and slicer)
Second dataset has 5 names and 5 surnames in it

If the sliceritem in the first dataset is missing in the second dataset it can't be selected and ideally, no sliceritems should be selected in the second.
However, pivottables demand for at least one sliceritem to be selected.

I therefor made a 6th name in the second dataset, and named it DUMMY.
So now each time the non existing item of slicer 1 in slicer 2 is selected it SHOULD select the dummy item, however it results still in selecting all items of the secons data set

Below the code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sc1 As SlicerCache
Dim sc2 As SlicerCache
Dim si1 As SlicerItem
Dim si2 As SlicerItem

Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Name")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Name1")

Application.ScreenUpdating = False
Application.EnableEvents = False

sc2.ClearManualFilter


On Error Resume Next
For Each si2 In sc2.SlicerItems
Set si1 = sc1.SlicerItems(si2.Name)
If Not si1 Is Nothing Then
si2.Selected = si1.Selected
Else
si2.Selected = False
End If
Next si2
On Error GoTo 0

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I hope someone can help me out here

 

[Worf]

Hello

Did you manage to solve this?