Results 1 to 8 of 8

Thread: Passing a variable to a pivot filter.

  1. #1
    Board Regular tbablue's Avatar
    Join Date
    Apr 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Passing a variable to a pivot filter.

    Hi Forum,

    Some help req'd with passing a variable to a pivot filter.

    How would I amend the following code such that PivotItems("99999") could be replaced by the value from a cell - a named range, [CandID_Picker]? The Pivot Table needs to filter on CandID_Picker - and nothing else.

    With Activesheet.PivotTables("CandID_Picker_table").Pivotfields("CandID")
    .ClearAllFilters
    .PivotItems("99999").Visible = True
    End With

    Any help appreciated.
    dOH!

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Passing a variable to a pivot filter.

    Try just:

    Code:
    .PivotItems("" & [CandID_Picker]).Visible = True
    although if you've just cleared all filters, I don't know why it wouldn't already be visible.

  3. #3
    Board Regular tbablue's Avatar
    Join Date
    Apr 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Passing a variable to a pivot filter.

    TY for your attention Rory. Most grateful.

    I want to make all other PivotItems.Visible = False
    dOH!

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,234
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Passing a variable to a pivot filter.

    Try this

    If the data to be filtered does not exist in the pivot table, an error is generated, because you must select at least one data.
    With the following check if the data does not exist, then sends the message "No match".

    Or maybe you can verify the data in the original database, before filtering.

    Code:
    Sub Filter_Pivot()
      Dim pi As PivotItem, n As Long
      
      Application.ScreenUpdating = False
      n = 0
      With ActiveSheet.PivotTables("CandID_Picker_table").PivotFields("CandID")
        .ClearAllFilters
        For Each pi In .PivotItems
          If LCase(pi) <> LCase([CandID_Picker]) Then
            n = n + 1
            If n < .PivotItems.Count Then
              pi.Visible = False
            Else
              MsgBox "No match"
              .ClearAllFilters
            End If
          End If
        Next
      End With
    End Sub
    Regards Dante Amor

  5. #5
    Board Regular tbablue's Avatar
    Join Date
    Apr 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Passing a variable to a pivot filter.

    Hi Dante,

    Really grateful for your attention.

    The code fails at pi.Visible = False - I can't immediately see why.

    Any idea?
    dOH!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,234
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Passing a variable to a pivot filter.

    I made the comment at the same time I answered, we must first look for the data.

    Try this

    Code:
    Sub Filter_Pivot()
      Dim pi As PivotItem, f As Range
      Application.ScreenUpdating = False
      With ActiveSheet.PivotTables("CandID_Picker_table").PivotFields("CandID")
        .ClearAllFilters
        Set f = Range(.DataRange.Address).Find([CandID_Picker], , xlValues, xlWhole)
        If f Is Nothing Then
          MsgBox "No match"
        Else
            For Each pi In .PivotItems
              If LCase(pi) <> LCase([CandID_Picker]) Then pi.Visible = False
            Next
        End If
      End With
    End Sub
    Regards Dante Amor

  7. #7
    Board Regular tbablue's Avatar
    Join Date
    Apr 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Passing a variable to a pivot filter.

    Really grateful for your attention. Problem resolved.

    Thank you!
    dOH!

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,234
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Passing a variable to a pivot filter.

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •