One Slicer to control 2 Pivot Tables with different data source

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365
Hi,

I have 2 pivot tables with different data sources and i am trying to control pvt tables with one slicer. I used power query to add the tables to data model. My issue is i cant connect 2 tables in power pivot as it doesnt let me create many to many relations. Did anyone come across a similar issue and solve for it.

Thank you
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365
Hi Derek,

I couldnt get it to work. I am getting run time error 91 message....i have 8 slicers and 2 pivot tablesbased on 2 different data source. My slicer names are category, brand, manufacturer. am i supposed to use Slicer_Category, Slicer_Manufacturer as slicer cache names or just category, brand manufacturer? also where do i put the pivot table name in the code.
Thank you for your help
 

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365

ADVERTISEMENT

Hi,

The error line is "For Each oSi In oScBrand1.SlicerItems"...Its Run Time Error '1004' Application-defined or object -defined error.

below is the code i am using



Dim mbNoEvent As Boolean


Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim oScCategory1 As SlicerCache
Dim oScManufacturer1 As SlicerCache
Dim oScBrand1 As SlicerCache
Dim oSc As SlicerCache
Dim oPT As PivotTable
Dim oSi As SlicerItem
Dim sBrand1 As String
Dim bUpdate As Boolean
If mbNoEvent Then Exit Sub
mbNoEvent = True
bUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each oSc In ThisWorkbook.SlicerCaches
For Each oPT In oSc.PivotTables
If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
If oSc.Name Like "*Brand1*" Then
Set oScBrand1 = oSc
ElseIf oSc.Name Like "*Category1*" Then
Set oScCategory1 = oSc
ElseIf oSc.Name Like "*Manufacturer1*" Then
Set oScManufacturer1 = oSc
End If
Exit For
End If
Next
If Not oScBrand1 Is Nothing And Not oScCategory1 Is Nothing And Not oScManufacturer1 Is Nothing Then Exit For
Next
If Not oScBrand1 Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScBrand1.Name, 7, 3) And oSc.Name <> oScBrand1.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScBrand1.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
If Not oScManufacturer1 Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScManufacturer1.Name, 7, 3) And oSc.Name <> oScManufacturer1.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScManufacturer1.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
If Not oScCategory1 Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScCategory1.Name, 7, 3) And oSc.Name <> oScCategory1.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScCategory1.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
mbNoEvent = False
Application.ScreenUpdating = bUpdate
End Sub
 

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
The following link describes how to post VBA code:
http://www.mrexcel.com/forum/board-...post-your-visual-basic-applications-code.html
You need to debug the code to see what it is doing - for example, you need to make sure that the correct slicer names are being found etc.
I have added some "Debug.Print" statements to the first part of the code to give you an idea of what I mean. Te output will be seen in the Immediate window.
Code:
Dim mbNoEvent As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oScCategory1 As SlicerCache
    Dim oScManufacturer1 As SlicerCache
    Dim oScBrand1 As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    Dim sBrand1 As String
    Dim bUpdate As Boolean
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    ' DEBUG added ================================================================
    For Each oSc In ThisWorkbook.SlicerCaches
        Debug.Print "Processing SlicerCache: " & oSc.Name
        For Each oPT In oSc.PivotTables
            Debug.Print "Processing PivotTable: " & oPT.Name
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                Debug.Print "PTs matched"
                If oSc.Name Like "*Brand1*" Then
                    Debug.Print "oScBrand1 set to: " & oSc.Name
                    Set oScBrand1 = oSc
                ElseIf oSc.Name Like "*Category1*" Then
                    Debug.Print "oScCategory1 set to: " & oSc.Name
                    Set oScCategory1 = oSc
                ElseIf oSc.Name Like "*Manufacturer1*" Then
                    Debug.Print "oScManufacturer1 set to: " & oSc.Name
                    Set oScManufacturer1 = oSc
                End If
                Exit For
            End If
        Next
        If Not oScBrand1 Is Nothing And Not oScCategory1 Is Nothing And Not oScManufacturer1 Is Nothing Then Exit For
    Next
    ' ===============================================================================
    If Not oScBrand1 Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScBrand1.Name, 7, 3) And oSc.Name <> oScBrand1.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                ' Problem here =====>
                For Each oSi In oScBrand1.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScManufacturer1 Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScManufacturer1.Name, 7, 3) And oSc.Name <> oScManufacturer1.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScManufacturer1.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScCategory1 Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScCategory1.Name, 7, 3) And oSc.Name <> oScCategory1.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScCategory1.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    mbNoEvent = False
    Application.ScreenUpdating = bUpdate
End Sub
 

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365

ADVERTISEMENT

Hi Derek,

I am still coming across the same issue error 1004 and following line is highlighted in yellow "For Each oSi In oScBrand1.SlicerItems". If it helps i can send you the file i am working on.

Thanks
 

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
So the error has changed from 91 to 1004?
What details were output from the Debug statements?
Sorry, but I do not download other peoples workbooks. Two reasons, macro security and data protection.
 

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365
It just says... Run-time error '1004' Application-defined or object -defined error. Thanks
 

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
Did you place that code in the ThisWorkbook module?
If you are not getting any output from the Debug.Print lines, then that means that is not finding any Slicer Caches.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,652
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top