0

I am trying to filter a pivot table based on the filter of another pivot table on a different sheet with a different data source.

I have two sheets (Main and Size Actual - Style) and want to filter by "Style". The Main sheet is where I want to choose the styles to filter by and from there I want to have the other sheet update with those selected styles filtered (would help if the filtering occurred with a button). I have changed some code to account for the field (Style) that I want to filter for. Right now I have the Main sheet pivot table resetting to show all values when a filter on style is selected on Size Actual - Style. I need that to be switched and instead of clearing the filter I want it to update with the selected styles from the filter.

Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean

On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
            pt.ManualUpdate = True
            Set pf = pt.PivotFields("Style")
                    bMI = pfMain.EnableMultiplePageItems
                    With pf
                        .ClearAllFilters
                        Select Case bMI
                            Case False
                                .CurrentPage = pfMain.CurrentPage.Value
                            Case True
                                .CurrentPage = "(All)"
                                For Each pi In pfMain.PivotItems
                                    .PivotItems("Style").Visible = 
pi.Visible
                                Next pi
                                .EnableMultiplePageItems = bMI
                        End Select
                    End With
                    bMI = False

            Set pf = Nothing
            pt.ManualUpdate = False
        End If
    Next pt
Next ws
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I am trying to have the filter on the second sheet update its filter based on the selection on the first sheets style filter.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Joe
  • 9
  • 6
  • You are making a change on the sheet; have you tried a change event? – Cyril Mar 29 '19 at 13:48
  • I am not sure if I know what you mean by that. Where would I make the changes to test it out? Sorry, I am new to VBA.. – Joe Mar 29 '19 at 13:51
  • see https://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change . Beyond that, just google "VBA Change Event" and you'll have a list of information and others' trial and error. – Cyril Mar 29 '19 at 13:56

0 Answers0