none
Filter data between two dates in Pivot Table (populated through Data Source) RRS feed

  • Question

  • Hi,

    We have a pivot table where we need to filter data between two dates. We do not want to use multi select filter option.

    We are using below VBA code to add filter on button click in Pivot Table. However we are getting 1004 error.

    Private Sub CommandButton1_Click()

     

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters. _

    Add Type:=xlDateBetween, Value1:=(Range("E6").Value), Value2:=(Range("F6").Value)

       

    End Sub

    Monday, October 13, 2014 10:08 AM

Answers

  • Hello,

    I was presuming your date field was a row-field. I don't think it's possible to filter a page field the way you want to do it.

    Also when you drag it to the row and put it back to the page, the filter is lost.

    I'm afraid you'll have to examine each item by code

    Monday, October 13, 2014 2:03 PM
  • Hi,

    As Wouter Defour said, we cannot set Date Filter for a field which is in the Filters area manually or programmatically. I think this behavior is by design. Once dragging a field into the Filters area, that field can only be filtered with the existing values list.

    Tuesday, October 14, 2014 8:50 AM
    Moderator

All replies

  • Hello,

    Works for me when I convert the dates to string:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters. _

    Add Type:=xlDateBetween, Value1:=(str(Range("E6").Value)), Value2:=(str(Range("F6").Value))

    Best regards,

    Wouter

    Monday, October 13, 2014 10:52 AM
  • Hi Wouter,

    Thanks for the quick response but the solution didn't work and i am still getting the 'Error:1004-application defined or object defined error'

    I have attached the screenshot of the pivot.

    The code on button click is:(Underlined and Bold is where the error is)

    Private Sub CommandButton1_Click()

     

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters. _

    Add Type:=xlDateBetween, Value1:=(Str(Range("B1").Value)), Value2:=(Str(Range("B2").Value))

     

    End Sub

    Monday, October 13, 2014 1:22 PM
  • Hello,

    I was presuming your date field was a row-field. I don't think it's possible to filter a page field the way you want to do it.

    Also when you drag it to the row and put it back to the page, the filter is lost.

    I'm afraid you'll have to examine each item by code

    Monday, October 13, 2014 2:03 PM
  • Excel 2010 with free PowerPivot Add-In
    Compatible with Office 2013 Pro Plus
    With DAX function DatesBetween()
    With Slicers.
    No VBA code.
    DataBase: AdventureWorks
    http://www.mediafire.com/view/1t8sjyds45386gc/06_05_13.xlsx

    Tuesday, October 14, 2014 1:36 AM
  • Hi,

    As Wouter Defour said, we cannot set Date Filter for a field which is in the Filters area manually or programmatically. I think this behavior is by design. Once dragging a field into the Filters area, that field can only be filtered with the existing values list.

    Tuesday, October 14, 2014 8:50 AM
    Moderator
  • You could always use (I think it's called a slave column) in col E- using an If statement- so if true input "Yes"

    Then if the pivot table selects Yes- from colE- Your form button could run the code below

    HTH

    David

     Dim lastrow As Long
    
        With Sheets("sheet1")
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
        End With
    
    Sheets("Sheet1").range("E" & lastrow).range ="=IF(AND(B5>B1,B5<B2),""Yes"","""")"
    

    es-- then you should be ok

    • Proposed as answer by David_1234 Wednesday, October 15, 2014 10:17 AM
    Tuesday, October 14, 2014 1:57 PM
  • @David_1234 this works and fixed my problem thanks.
    • Edited by dke-01 Wednesday, September 23, 2015 4:49 AM
    Wednesday, September 23, 2015 4:49 AM