Filtering Pivot Table Items by Referencing Cell String Values

Question

I am trying to filter a pivot table using the string value from a number of cells, which are stored in an array variable LTmonth(). However, when I plug this array variable into the code below, it doesn't read the array values for some reason and won't filter the values stored in that array.

One thing I noted, when I change LTmonth into an actual value of the filter such as shown below, the code works perfectly:

"If ActiveSheet.PivotTables("PivotTable1").PivotFields("MONTH").PivotItems(j) = "JAN-2016" Then"

Given that the cell values exactly match the filter items in the pivot table, how do I make the two values match each other? I've looked everywhere for an answer, but the answers I've found didn't help solve my problem. This is the closest I've gotten to making the code work, but I'm not sure why the variable array isn't working.

Sub Filter()

    Dim i, j, z, monthcount As Integer
    monthcount = 8

    Dim LTmonth() As String
    ReDim LTmonth(monthcount)

    For i = 1 To monthcount
        LTmonth(i) = ActiveSheet.Range("P2").Offset(i - 1, 0).Value
    Next i


    For j = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("MONTH").PivotItems.Count
        For z = 1 To monthcount
            If ActiveSheet.PivotTables("PivotTable1").PivotFields("MONTH").PivotItems(j) = LTmonth(z) Then
                ActiveSheet.PivotTables("PivotTable1").PivotFields("MONTH").PivotItems(j).Visible = True
            Else
                ActiveSheet.PivotTables("PivotTable1").PivotFields("MONTH").PivotItems(j).Visible = False
            End If
        Next z
    Next j


End Sub

Debug the code using F8 to see what's in array. And to compare two strings use InStr. — ManishChristian
How do you compare the string value of the filter items? I think this might be the case since the formatting of the date in the filter may be different than the actual format of the item. — Kevin Choi

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-04-03T16:44:07

See if that does what you are trying to achieve.

Sub Filter()
    Dim pt As PivotTable
    Dim pf As PivotField

    Dim i, j, z, monthcount As Integer
    monthcount = 8

    Dim LTMonth() As String
    ReDim LTMonth(monthcount)

    For i = 1 To monthcount
        LTMonth(i) = Format(ActiveSheet.Range("P2").Offset(i - 1, 0).Value, "mmm-yyyy")
    Next i

    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Month")
    pf.ClearAllFilters
    For j = 1 To pf.PivotItems.Count
        For z = 1 To monthcount
            If ActiveSheet.PivotTables("PivotTable1").PivotFields("MONTH").PivotItems(j) = LTMonth(z) Then
                ActiveSheet.PivotTables("PivotTable1").PivotFields("MONTH").PivotItems(j).Visible = True
                Exit For
            End If
        Next z
    Next j
End Sub

Filtering Pivot Table Items by Referencing Cell String Values

1 Answers