locked
Filtering pivot table based off cell value RRS feed

  • Question

  • Currently I'm working on a project where I have a "master" sheet, which has holds information about vendors which is linked to a database in Access. The datasheet and pivot table are refreshed from a connection to the database. What I'm looking to do is have either the value of a cell filter the Pivot table on the second tab. Below is the code that I currently have for this, but while I'm not getting an error it does not work. 

    "Cand Submitter Org Name" is the field name in the pivot table which im trying to filter
    "Pivot" is the name of the sheet of the pivot table.G
    G1 is the cell I'm looking to filter.

    Also I'm not sure if this is a possibility, but I have a column (B), which has the same values as the pivot table rows. Is it possible to create a link by clicking on a value in the column to automatically filter the pivot table based on a matching value?

    I really appreciate the help.



    Private Sub Worksheet_Change2(ByVal Target As Range)
    If Not Target.Address = Range("G1").Address Then Exit Sub
    Dim PT As PivotTable
    Dim ptItem As PivotItem
    On Error Resume Next
    For Each PT In Worksheets("Pivot").PivotTables
    With PT.PivotFields("Cand Submitter Org Name")
    If .EnableMultiplePageItems = True Then
    .ClearAllFilters
    End If
    Set ptItem = .PivotItems(Target.Value)
    If Not ptItem Is Nothing Then
    .CurrentPage = Target.Value
    End If
    End With
    Next
    End Sub


    • Edited by mtdeilus Wednesday, October 15, 2014 8:41 PM
    Wednesday, October 15, 2014 8:41 PM

All replies

  • Hello,

    You are not getting an error because you wrote: On Error Resume Next
    I think you can not set the page of a pivotfield, but you can on a pagefield.

    Try With PT.PageFields("Cand Submitter Org Name") instead of With PT.PivotFields("Cand Submitter Org Name")

    Best regards,

    Wouter

    Thursday, October 16, 2014 11:10 AM
  • Wouter,

    I appreciate the time and the response.

    I've gone ahead and included two screen caps. When I made the change you had suggested, nothing triggered in the workbook, and there were no errors when doing the debugging and step in of the code. The ultimate goal is to get cell G1 from the first tab of the workbook, "Query sample" to trigger the filter when the user enters in text that matches an item in the pivot table. Let me know if you have any questions if I'm unclear in trying to explain this.

    Matt

    Thursday, October 16, 2014 5:22 PM
  • Hello,

    This works for me:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("G1").Address Then Exit Sub
    Dim PT As PivotTable

    For Each PT In Worksheets("Pivot").PivotTables
    With PT.PageFields("Cand Submitter Org Name")
    If .EnableMultiplePageItems = True Then
    .ClearAllFilters
    End If
    'Set ptItem = .pageitems(Target.Value)

    .CurrentPage = Target.Value

    End With
    Next
    End Sub

    I erased the following part: Set ptItem = .PivotItems(Target.Value)
    What were you planning on doing here?

    Hope it helps!

    Friday, October 17, 2014 11:45 AM
  • I appreciate the response. I was trying 

    When running this code, I'm recieving the error message: 

    Run-time error '1004':Unable to get the PageFields property of the PivotTable class error message.

    My code seems to match all the fields in the pivot table, not sure why this is occuring. You could very well be right. I'm still new at VBA, and researched the block needed to make this work.

    Friday, October 17, 2014 3:21 PM
  • Hello,

    Your pagefield must have another name than "Cand Submitter Org Name". In your screenshot I can see Vendor and COM. Try replcing the name by one of these.

    Best regards,

    Wouter

    Tuesday, October 21, 2014 11:06 AM