Dynamic pivot filtering - Cell reference (VBA)

[Newlivez]

Hi everyone,

I have a little questions regarding filtering pivot tables automatically based on input from a single data validation cell outside of the pivot table (as opposed to selected the filter from a drop-down menu.

I already brwosed the forums here and fround a relatively similar topic (http://www.mrexcel.com/forum/excel-questions/625300-pivot-table-filter-reference-cell.html). However, I unfortuantely did not succeed to adapt the proposed solutions to my own situation. Perhaps someone here is willing to help me out.

What I am looking for:

• I have one sheet("Pivots") in my workbook with several Pivottables.
• On my worksheet "Dashboard" I have a dropdown/data validation menu in cell B1, in which users can select a period (month+year). This generates a text string in C1 with a similar value. (I know - a bit tedious, chose this for several other reasons). Cell C1 is also a named range, "SelectedPeriod".
• I want to filter my pivottables on the "Pivots" sheet based on the value in cell C1 (other filters are already present as well, they should stay where they are). The relevant fieldname in the pivottabel is "PeriodVBA". If there is no entry in cell C1 no additional filtering should occur.

I hope someone is able to help me with this little challenge/adapt the earlier proposed codes to fit my situation. Anyways, thanks a lot for looking into it!

 

[Newlivez]

Ok, this is how far I got.

On the Data validation worksheet module I have posted the following code:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim sField As String, sDV_Address As String
    
    sField = "VBAPeriod"  'Field Name
    sDV_Address = "$C$1" 'Cell with DV dropdown to select filter item.


    If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
        Target.Cells.Count > 1 Then Exit Sub
    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    Call Single_Page_Filter(Sheets("Pivots") _
        .PivotTables(1).PivotFields(sField), Target.Text)
       
CleanUp:
    Application.EnableEvents = True
End Sub

Additionally, in a standard module field I have included the following code:

Public Function Single_Page_Filter(pvtField As PivotField, _        ByVal sValue As String)
    
    On Error GoTo ErrorHandler
    With pvtField
        .ClearAllFilters
        .CurrentPage = sValue
    End With
    Exit Function


ErrorHandler:
    Select Case Err.Description
        Case "Application-defined or object-defined error"
            MsgBox "PivotItem: " & sValue & " not found in PivotTable: " _
                & pvtField.Parent.Name
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
End Function

*Something* works now. The issues I'm still encountering are:

• The Reference cell ($C$1) has to be a text string. I originally wanted to reference to an Index-Match value, this didnt work. When making it a hard value I got some output. Can this be solved?
• Current code only updates one pivottable on my worksheet. I don't know how that one is being selected, as it is not even the pivottable with the lowest number (standard names). I need to update all pivottables though. Anyone got pointers on that?

 

[Newlivez]

Sorry for the spam guys, I got some news again (and I unfortunately can't find where to edit my posts). To prevent people from solving problems I already solved:


On the Data validation worksheet module I have posted the following code:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim sField As String, sDV_Address As String
    
    sField = "VBAPeriod"  'Field Name
    sDV_Address = "$C$1" 'Cell with DV dropdown to select filter item.


    If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
        Target.Cells.Count > 1 Then Exit Sub
    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    Call Single_Page_Filter(Sheets("Pivots").PivotTables("PivotHoursPerson") _
        .PivotFields(sField), Target.Text)
    Call Single_Page_Filter(Sheets("Pivots").PivotTables("PivotNERPerson") _
        .PivotFields(sField), Target.Text)
    Call Single_Page_Filter(Sheets("Pivots").PivotTables("PivotSERPerson") _
        .PivotFields(sField), Target.Text)
    Call Single_Page_Filter(Sheets("Pivots").PivotTables("PivotHoursStream") _
        .PivotFields(sField), Target.Text)
    Call Single_Page_Filter(Sheets("Pivots").PivotTables("PivotNERStream") _
        .PivotFields(sField), Target.Text)
    Call Single_Page_Filter(Sheets("Pivots").PivotTables("PivotSERStream") _
        .PivotFields(sField), Target.Text)
CleanUp:
    Application.EnableEvents = True
End Sub

This solved the issue of just the one random pivottable updaing, I decided to just add more calls and reference to all pivottables. Probably the most stupid and unelegant solution possible but hey, I'm no VBA genius . If anyone has tips how to do it with a loop orso Id be glad to hear.

The only problem that does remain is the one about referencing to formula / text string:

• The Reference cell ($C$1) has to be a text string. I originally wanted to reference to an Index-Match value, this didnt work. When making it a hard value I got some output.

I have a solution direction in mind, but don't know how to include that in the code. My hypothesis is that the problem with making the reference cell a formula, that I will no longer detect any changes in VBA code, and therefore no longer update the pivottables.

What a solution could be is to change the value that gets tested for changes to cell B1, while the value used for the filtering (the Target.Text) would still refer to cell C1.

Anyone who can give feedback on that and help me craft the necessary code?