VBA: Filter Pivot Table based on cell values

**nicklasj** · 08-31-2015, 09:04 AM

Hello,

I'm having some trouble filtering Row Labels based on cell values since I am not able to use .CurrentPage since this seems to only apply for Report Filter. I have three fields in Row Labels and just one field in Values, basically I want to be able to filter the pivot table from another sheet depending on three different cell values.

Reason why I dont have the fields in Report Filter but in Row Labels is that I still want to see all the rows in the pivot, not only the total sum (to be able to create charts later on or automatically).

Would very much appreciate your help on this one since I havent found any solution on this forum and google.

Br,
Nicklas

**berlan** · 08-31-2015, 09:54 AM

Hi Nicklas,

Could you upload a sample workbook without any sensitive data and explain expected result? Would be easier for us to work with

Mvh,
Johan

**nicklasj** · 08-31-2015, 10:38 AM

Sure! Find enclosed the example. I'm trying to figure out if it's possible to Filter as single selection from the yellow marked cells, with the fields still as RowLabels and not in Report Filter.

**berlan** · 08-31-2015, 11:16 AM

One way is to use a worksheet change event to be triggered when either cell S2 or V2 changes.

Put this in the Sheet1 module (and see attached):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Integer
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Union(Range("S2"), Range("V2"))) Is Nothing Then Exit Sub
        
    Application.ScreenUpdating = False
    On Error Resume Next
    With ActiveSheet.PivotTables("PivotTable1").PivotFields(IIf(Target.Address = "$S$2", "Market", "Customer"))
        For i = .PivotItems.Count To 1 Step -1
            If LCase$(.PivotItems(i)) = LCase$(Target.Value) Or Target.Value = vbNullString Then
                .PivotItems(i).Visible = True
            Else
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
    On Error GoTo 0
    Application.ScreenUpdating = True

End Sub

See how that works for you.