Как выбрать элемент среза, который зависит от выбранного значения другого среза в VBA? (Работа с кубами OLAP)

Хорошо, попробуем это:

Option Explicit
Sub Main()

    Dim wb As Workbook
    Dim slItem As SlicerItem
    Dim sc3 As SlicerCache
    Dim sc3L As SlicerCacheLevel

    Set wb = ActiveWorkbook 'ThisWorkbook is better if this code is run on the same workbook
    Set sc3 = wb.SlicerCaches("Slicer_Primary_Account_List_Combo__BI")

    sc3L.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData

    ' Select the first person within the Sales Cube slicer
    ' This selects each slicer item in the Sales Cube and iterates through them
    For Each slItem In sc3L.SlicerItems
        If slItem.HasData Then   ''' This ensures the iteration is only on items with data
            sc3.ClearManualFilter
            sc3.VisibleSlicerItemsList = Array(slItem.Name)
            Testing CStr(slItem.Name)
        End If
    Next
End Sub
Sub Testing(SalesName As String)

    Dim slItem2 As SlicerItem
    Dim sc4 As SlicerCache
    Dim sc4L As SlicerCacheLevel
    Dim wb As Workbook
    Dim SalesSplit As Variant

    'with this you are getting an array with the number and name being SalesSplit(1) the name
    'So taking that SalesName =  "123C - Smith, Bob" then SalesSplit(0) will be "123C" and SalesSplit(1) will be "Smith, Bob"
    SalesSplit = Split(SalesName, " - ")


    Set wb = ThisWorkbook 'if the workbook is the same containing the code
    Set sc4 = wb.SlicerCaches("Slicer_TM_Hierarchy")
    Set sc3L = sc3.SlicerCacheLevels(1)
    Set sc4L = sc4.SlicerCacheLevels(3)

    For Each slItem2 In sc4L.SlicerItems
        sc4.ClearManualFilter ''' CODE WORKS UP TO HERE
        If slItem2.Name = SalesSplit(1) Then   ''' I am trying to force the selection on the second slicer by looking for the name match. But this is NOT working. BREAKPOINT.
            slItem2.Selected = True
        End If
    Next

    'Continue your code, when this sub ends it will loop through the next item on your sales cube

End Sub

2 подпрограммы, первая из которых «основная» назовет вторую «Тестирование».

Шаг 1 Выберите товар с данными в кубе продаж.

Шаг 2 Вызвать процедуру тестирования, передав выбранное имя из куба продаж. Затем вы выбираете тот же элемент на втором кубе и выполняете свои задачи. После этого вернитесь к шагу 1