Help me Sync 2 Slicers with Different Data Sources

I've attached a Sample workbook with the code that I'm using, here is a description of my process:
1. Import External Data into Excel Table called "CleaningTable"
2. Clean data, Add calculated columns, etc...
3. Export CleaningTable to PowerPivot called "CleanedData"
4. Build Slicer1 off of RegionCode in CleanedData
5. Build Slicer2 off of RegionCode in CleaningTable
7. Attempt to sync slicers using below code:
Private Sub Worksheet_PivotTableUpdate _
    (ByVal Target As PivotTable)
Dim wb As Workbook
Dim scShort As SlicerCache
Dim scLong As SlicerCache
Dim siShort As SlicerItem
Dim siLong As SlicerItem

On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ThisWorkbook
Set scShort = wb.SlicerCaches("Slicer_RegionCode")
Set scLong = wb.SlicerCaches("Slicer_RegionCode1")

scLong.ClearManualFilter

For Each siLong In scLong.VisibleSlicerItems
    Set siLong = scLong.SlicerItems(siLong.Name)
    Set siShort = Nothing
    On Error Resume Next
    Set siShort = scShort.SlicerItems(siLong.Name)
    On Error GoTo errHandler
    If Not siShort Is Nothing Then
        If siShort.Selected = True Then
            siLong.Selected = True
        ElseIf siShort.Selected = False Then
            siLong.Selected = False
        End If
    Else
        siLong.Selected = False
    End If
Next siLong

exitHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

errHandler:
    MsgBox err.Description
    Resume exitHandler

End Sub

Open in new window

I keep getting "Application-define or object-defined error".

End-User selections would always be in Slicer_RegionCode on the Lead worksheet. I would like Slicer_RegionCode1 on the Follow worksheet to mimic the selections.  Both data sources have the same fields and data, the only reason I'm doing this is because you can't "showdetails" on data in PowerPivot that has been filtered by more than 1 Slicer.
SyncSlicerProblem.xlsm
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

-PolakAuthor Commented:
Things I've learned in the past Day....
For Slicers connected to PowerPivot (OLAP) tables the .VisibleSlicerItemsList property is only one that can be viewed.

Add this code to my sample workbook and run it after making a selection in Slicer_RegionCode.
Sub View VisibleSlicerItemsList
Dim scLong As SlicerCache
Dim v As Variant

Dim wb As Workbook
Dim siLong As SlicerItem
Dim s As Slicer
Set wb = ThisWorkbook
Set scLong = wb.SlicerCaches("Slicer_RegionCode")
v = scLong.VisibleSlicerItemsList
Dim ss As String
ss = v(1)
MsgBox ss
End Sub

Open in new window

According to what I'm reading I can then Split the value of the VisibleSlicersItemsList to my other non-OLAP slicers. (I don't know how to write the VBA to do that though)... but before you try and do it for me....

It doesn't appear that the property value for VisibleSlicersItemsList can articulate multiple filtering critera. Ala, if your selection is A & D in the Lead Slicer's VisibleSlicersItemsList it will only provide you with
[CleanData].[RegionCode].&[A]

Open in new window

I believe I've come full circle with the problem I was trying to work around.... MDX not allowing multiple filtering critera to show details on OLAP data...

If an expert would confirm that I'm shit-out-of-luck; I'll award points and begin the process of rebuilding my workbook so that all slicers data sources are based off of the CleaningTable rather than PowerPivot. This really sucks.
-PolakAuthor Commented:
Learned more things today... appearently you can write an array for VisbileSlicerItemsList to pull out mulitple selections in the OLAP slicer. For example,
sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]”, “[Date].[Calendar Year].&[2006]”)

Open in new window



However, adapting this all to my sample workbook code is all a way bit over my head! Anybody out there that knows what I'm talking about?
-PolakAuthor Commented:
Progress!
I've gotten this far with my code, (updated Sample workbook attached), it correctly passes some of the OLAP slicer's sliceritems to the non-OLAP slicer; however, not all the behavior follows correctly. For example....

When any single selection is made from the OLAP slicer, the Regular Slicer follows it correctly. When you hold CTRL when all Slicer Items are selected, only deselecting D & E will Follow correctly; whereas, deselecting A,B,or C will not Follow into the Regular Slicer. Selecting A and then holding CTRL and Selecting B will Follow correctly; however, selecting B and then holding CTRL and Selecting C will not Follow correctly. There are many other instances that do not Follow correctly.

Can anyone help me get across the finish line!?!?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim scOLAP As SlicerCache
Dim scList As SlicerCache
Dim sO As Slicer
Dim sL As Slicer
Dim si As SlicerItem
Dim i As Integer
Dim svalue As String

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_RegionCode")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_RegionCode1")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)

For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)

svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[CleanedData].[RegionCode].&[", ""), "]", "")

For Each si In scList.SlicerItems
If svalue = si.SourceName Then
si.Selected = True
Else
si.Selected = False
End If

Next

Next

End Sub

Open in new window

SyncSlicerProblem-07.05-2015.xlsm
-PolakAuthor Commented:
Finally, got through this one; big prop's to Erik Svensen's Blog Post for helping me with the code.

If anyone is trying to do this and has found googling the problem maddening, here is the final working code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim scOLAP As SlicerCache
Dim scList As SlicerCache
Dim sO As Slicer
Dim sL As Slicer
Dim si As SlicerItem
Dim i As Integer
Dim svalue As String
Dim ar() As String

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_RegionCode")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_RegionCode1")


scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[CleanedData].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
This bit of code will sync your OLAP Slicers to your Non-OLAP slicers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.