none
VBA Pivot Table Filter Between Two Dates RRS feed

  • Question

  • Hey,

    Im trying to create a VBA to filter a pivot table based on a "Date From" and "Date To" cells.  The Cells are B1/B2 respectively. and the pivot table is created as "Ship Date US Format" as the "date" and customer name and value in the rows and values section.

    The below is what I have so far but I keep getting a debug and I cant think how to fix.

    Sub FilterPivotTable()

        Dim startDate As Date
         Dim endDate As Date

    startDate = Range("B1")
    endDate = Range("B2")

    ActiveSheet.PivotTables("PivotTable1").PivotFields ("Ship Date US Format")
    PivotFilters.Add Type:=xlDateBetween, Value1:=startDate, Value2:=endDate


     
     End Sub

    Monday, August 22, 2016 10:52 AM

Answers

  • Try this version:

    Sub FilterPivotTable()
        Dim startDate As String
        Dim endDate As String
        startDate = Format(Range("B1").Value, "Short Date")
        endDate = Format(Range("B2").Value, "Short Date")
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Ship Date US Format").PivotFilters
            Do While .Count > 0
                .Item(1).Delete
            Loop
            .Add Type:=xlDateBetween, Value1:=startDate, Value2:=endDate
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 22, 2016 2:17 PM