CODEWITHSUNDEEP
arraysexcelvbafilterpivot-table
Kevin Choi
Kevin Choi

Reputation: 11

Filtering Pivot Table Items by Referencing Cell String Values

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

Upvotes: 1

Views: 934

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

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

Upvotes: 0

Related Questions