pivot filter based on range

[daveyc18]

right now i have this...

  With pf
        For Each pi In pf.PivotItems
        If pi.Name = "811" or pi.name = "911" Then
        pi.Visible = True
        Else
        pi.Visible = False
        End If
        Next pi
    End With

but i'd like this to be more flexible and dynamic. so, rather than hardcode the item "811" and "911"....i'd like the macro to filter the report filter items by looping through a range somewhere in the spreadsheet.

so, let's say in sheet1 column A....I have 811 in A1, 911, in A2, and perhaps more in the future, and I gave it a named range "filter" (which uses an offset formula to make the named range dynamic). So the macro filters the report filter based on the named range "filter."

 

[DanteAmor]

Try this

Change data in blue by your information.

Sub Filter_Pivot()
  Dim tb As PivotTable, pf As PivotField, pi As PivotItem
  Dim f As Range, r As Range, r2 As Range, exist As Boolean, c As Range
  Application.ScreenUpdating = False
  '
  Set tb = ActiveSheet.PivotTables("[COLOR=#0000ff]Table1[/COLOR]")
  Set pf = tb.PivotFields("[COLOR=#0000ff]ID[/COLOR]")
  Set r = Range("[COLOR=#0000ff]filter[/COLOR]")    'It can be on the same sheet or on any other sheet of the book.
  With pf
    .ClearAllFilters
    Set r2 = .DataRange
    For Each c In r
      Set f = r2.Find(c, , xlValues, xlWhole)
      If Not f Is Nothing Then
        exist = True
        Exit For
      End If
    Next
    If exist = False Then
      MsgBox "[COLOR=#0000ff]The data to be filtered does not match[/COLOR]"
      Exit Sub
    End If
    For Each pi In .PivotItems
      exist = False
      For Each c In r
        If LCase(pi) = LCase(c) Then
          exist = True
          Exit For
        End If
      Next
      If exist = False Then pi.Visible = False
    Next
  End With
End Sub

 

[daveyc18]

Try this

Change data in blue by your information.

Sub Filter_Pivot()
  Dim tb As PivotTable, pf As PivotField, pi As PivotItem
  Dim f As Range, r As Range, r2 As Range, exist As Boolean, c As Range
  Application.ScreenUpdating = False
  '
  Set tb = ActiveSheet.PivotTables("[COLOR=#0000ff]Table1[/COLOR]")
  Set pf = tb.PivotFields("[COLOR=#0000ff]ID[/COLOR]")
  Set r = Range("[COLOR=#0000ff]filter[/COLOR]")    'It can be on the same sheet or on any other sheet of the book.
  With pf
    .ClearAllFilters
    Set r2 = .DataRange
    For Each c In r
      Set f = r2.Find(c, , xlValues, xlWhole)
      If Not f Is Nothing Then
        exist = True
        Exit For
      End If
    Next
    If exist = False Then
      MsgBox "[COLOR=#0000ff]The data to be filtered does not match[/COLOR]"
      Exit Sub
    End If
    For Each pi In .PivotItems
      exist = False
      For Each c In r
        If LCase(pi) = LCase(c) Then
          exist = True
          Exit For
        End If
      Next
      If exist = False Then pi.Visible = False
    Next
  End With
End Sub

it didn't work initially because my filter is a "report filteR" and not a "row label." however, i simply made it a "row label" isntead and the code works fine. i assume it didn't work initially because "r2" looks at the data range , so the pivotfield has to be a row or column and not a report filter.

thanks a lot.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[daveyc18]

I'm glad to help you. Thanks for the feedback.

actually, how would you modify the code so that it does the opposite: based on that Range("filter"), EXCLUDE those items from the pivot.

 

[DanteAmor]

actually, how would you modify the code so that it does the opposite: based on that Range("filter"), EXCLUDE those items from the pivot.

Hi @daveyc18, try this:

Sub Filter_Pivot()
  Dim tb As PivotTable, pf As PivotField, pi As PivotItem
  Dim f As Range, r As Range, r2 As Range, exist As Boolean, c As Range
  Application.ScreenUpdating = False
  '
  Set tb = ActiveSheet.PivotTables("Table1")
  Set pf = tb.PivotFields("ID")
  Set r = Range("filter")    'It can be on the same sheet or on any other sheet of the book.
  With pf
    .ClearAllFilters
    
    For Each pi In .PivotItems
      exist = False
      For Each c In r
        If LCase(pi) = LCase(c) Then
          exist = True
          Exit For
        End If
      Next
      If exist = [COLOR=#ff0000][B]True [/B][/COLOR]Then pi.Visible = False
    Next
  End With
End Sub