CODEWITHSUNDEEP
vbaexcelpivot
calicationoflife
calicationoflife

Reputation: 291

VBA: filter pivot table according to cell value

I have a pivot table which I want to dynamically filter based on the last two days (so for 4th of Jan it'd be 2nd and 3rd of Jan.). I already have the VBA code down to insert the newest pivot data (into the HISTORICALS sheet). Then, I retrieve the last two days (variables defined as Highest_Max & Second_Highest_Max).

My idea was then to paste the two newest dates into cells in the PIVOT sheet (where the pivot is located) and filter the pivot based on the value in these cells (B34 & B35). Unfortunately, the filtering option is where my code is stuck:

Sub Select_Last_Two_Days()


    With Worksheets("HISTORICALS")

' select the two newest dates

    Highest_Max = Format(WorksheetFunction.Max(.Range("A:A")), "Short Date")
    Second_Highest_Max = Format(WorksheetFunction.Large(.Range("A:A"), WorksheetFunction.CountIf(.Range("A:A"), WorksheetFunction.Max(.Range("A:A"))) + 1), "Short Date")
    Debug.Print Highest_Max, Second_Highest_Max

' paste results into cells in PIVOT sheet
    Worksheets("PIVOT").Range("B34").Value = Highest_Max
    Worksheets("PIVOT").Range("B35").Value = Second_Highest_Max
End With

' update pivot according to cell values (B34/B35)

With Worksheets("PIVOT").PivotTables(PivotTable1)
    .PivotFields("ipg:date").ClearAllFilters
    .PivotFields("ipg:date").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[B34].Value, Value2:=[B35].Value
End With

End Sub

I receive the following error: "Unable to get the PivotTables property of the Worksheet class."

When I click debug, the following line is shown to be wrong:

With Worksheets("PIVOT").PivotTables(PivotTable1)

Any idea what I'm doing wrong? Been researching for the last couple of hours, but can't manage to find a fix. Any help is appreciated.

Upvotes: 0

Views: 4365

Answers (1)

Jpad Solutions
Jpad Solutions

Reputation: 332

I think you want something more like this:

    Dim pvItem As PivotItem

        'make all values in the filter true to start with
        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Date")

            For Each pvItem In .PivotItems
                 pvItem.Visible = True
            Next pvItem

        End With

        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Date")
            For Each pvItem In .PivotItems

                If Format(pvItem.Name, "dd/mm/yyyy") <> Sheets("PIVOT").Range("B34") And Format(pvItem.Name, "dd/mm/yyyy") <> Sheets("PIVOT").Range("B35") Then
                    pvItem.Visible = False
                End If

            Next pvItem

        End With

The date format will need changing to suit your needs - but in essence, you need to make all items ticked in the filter, before you can start. Then untick all apart from the 2 you want

THIS CODE WORKS ON YOUR FILE - ONCE YOU RECREATE THE PIVOT TABLE (changing the sheet name, and the pivot table name...)

        Sub Pivot_Test()

        Dim pvItem As PivotItem

                'make all values in the filter true to start with

                    ActiveSheet.PivotTables("PivotTable2").RefreshTable

            With ActiveSheet.PivotTables("PivotTable2").PivotFields("ipg:date")

                For Each pvItem In .PivotItems

                            pvItem.Visible = True

                    Next pvItem

            End With

            Dim dte1 As Date
            Dim dte2 As Date

            dte1 = CDate(Sheets("Sheet2").Range("B34"))
            dte2 = CDate(Sheets("Sheet2").Range("B35"))

                With ActiveSheet.PivotTables("PivotTable2").PivotFields("ipg:date")
                    For Each pvItem In .PivotItems

                        If Format(pvItem.Name, "mm/dd/yyyy") <> dte1 And Format(pvItem.Name, "mm/dd/yyyy") <> dte2 Then
                            pvItem.Visible = False
                        End If

                    Next pvItem

                End With

        End Sub

Upvotes: 1

Related Questions