0

I'm trying to set-up some VBA code that will allow me to control multiple pivot tables (and data sources) with 1 slicer.

In the past, I have only needed to implement VBA code that controls 1 additional slicer, but now I am trying to set-it up to control 2 slicers and am running into issues.

Here is my code that I used in the past for controlling 1 slicer:

As a module:

Public PrevCat As String

In ThisWorkbook:

Private Sub Workbook_Open()
    PrevCat = Sheet27.Range("O5").Value
End Sub

Primary code:

Option Explicit

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False


Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

NewCat = Sheet27.Range("O5").Value

    If NewCat <> PrevCat Then
        Application.EnableEvents = False
        Set pt = Sheet27.PivotTables("Pivot Match 2")
        Set Field = pt.PivotFields("Region")
        With Field
            .ClearAllFilters
            On Error Resume Next
            .CurrentPage = NewCat
            On Error GoTo 0
        End With
        pt.RefreshTable
        PrevCat = NewCat
        Application.EnableEvents = True
    End If
Application.ScreenUpdating = True

End Sub

Like I said, this code works perfectly for controlling 1 additional slicer. However, I need the code to control 2 slicers. All i did was add an additional If statement, but it doesn't seem to work:

Option Explicit

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False


Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

NewCat = Sheet27.Range("O5").Value

    If NewCat <> PrevCat Then
        Application.EnableEvents = False
        Set pt = Sheet27.PivotTables("Pivot Match 2")
        Set Field = pt.PivotFields("Region")
        With Field
            .ClearAllFilters
            On Error Resume Next
            .CurrentPage = NewCat
            On Error GoTo 0
        End With
        pt.RefreshTable
        PrevCat = NewCat
        Application.EnableEvents = True
    End If

    If NewCat <> PrevCat Then
        Application.EnableEvents = False
        Set pt = Sheet27.PivotTables("Pivot Match 3")
        Set Field = pt.PivotFields("Region")
        With Field
            .ClearAllFilters
            On Error Resume Next
            .CurrentPage = NewCat
            On Error GoTo 0
        End With
        pt.RefreshTable
        PrevCat = NewCat
        Application.EnableEvents = True
    End If


Application.ScreenUpdating = True

End Sub

Any ideas on how i can get this to work?

Community
  • 1
  • 1
ranopano
  • 509
  • 2
  • 16
  • 32
  • there seems to be some confusion in the question. You start with one slicer controlling mulitple pivottables as an aim. Then you move onto multiple slicers. If one slicer, create all pivottables from same source then simply add each pivottable to control via slicer using the slicer's report connections facility. – QHarr Mar 09 '18 at 23:38
  • Sorry wasn't very clear. There are 3 data souces. I'm using excel 2010, so there is no data model option and powerpivot isn't an option either. Basically I need the slicer from 1 data source to control 2 other slicers from other data sources – ranopano Mar 09 '18 at 23:40
  • Can you use the pivottable update event of the pivottable associated with the slicer to impact the other pivottables? You can sync that way. Sure I have done that before. – QHarr Mar 09 '18 at 23:41
  • Btw, that will update the slicers connected to their respective tables if set up correctly. – QHarr Mar 09 '18 at 23:59
  • It seems duplicate question. Refer this link. https://stackoverflow.com/questions/13120957/using-single-slicer-to-control-two-pivot-tables-with-different-data-source-in-ex – Parveen Saroha Mar 10 '18 at 02:53
  • QHarr- Never used pivottable update much in the past, so I'll look into it. A concern is that there are A LOT of pivot tables in this report. If i do the pivottable update event, will I have reference each pivottable individually? – ranopano Apr 12 '18 at 14:34

0 Answers0