locked
Set a pivot table filter value from a cell RRS feed

  • Question

  • I've got a pivot table in Excel 2013.  It's got a Pivot Table named "ReportDate" on a Sheet named 'Report".  There's a filter named 'CompletedDate'.   I'm trying to set that filter date to the value in a cell.  I keep getting an error:

    Dim PvtTbl As PivotTable

    Set PvtTbl = Sheets("report").PivotTables("ReportDate")

     ' the next line works and changes the filter from it's current date to "All"

            PvtTbl.PivotFields("CompletedDate").ClearManualFilter


    ' the next line produces an error

            PvtTbl.PivotFields("CompletedDate").PivotFilters.Add Type:=xlSpecificDate, Value1:=Sheets("Report").Range("C1")

    I've tried Value1:="09/18/15" - but that produces the same error

    Any ideas what I'm doing wrong?

     


    ajw

    Tuesday, August 11, 2015 3:45 AM

Answers

  • Is CompletedDate a Filter field? If so, try

        PvtTbl.PivotFields("CompletedDate").CurrentPage = Sheets("report").Range("C1").Value


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

    Tuesday, August 11, 2015 5:42 AM