+ Reply to Thread
Results 1 to 8 of 8

Filter Pivot Table date column by labels using data in other cells

  1. #1
    Registered User
    Join Date
    04-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Filter Pivot Table date column by labels using data in other cells

    Hi,

    This is my first post, although I have spent a great deal of time browsing other posts for solutions to my problems. I have learned a lot.

    On a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates.
    I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.

    I have attached the following code to a button on a different sheet.

    The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.
    I am having trouble with passing the start and end dates to my pivot table filter.
    I do not get any errors, the filter is simply blank.

    Any insight would be appreciated.


    Private Sub CommandButton3_Click()

    Sheets("Details").Select

    Dim pt As PivotTable
    Dim Employee As String
    Dim SDate As String
    Dim EDate As String

    Set pt = Worksheets("Details").PivotTables("PivotTable1")
    Employee = Range("EmpName").Value
    SDate = Range("D4")
    EDate = Range("D5")

    pt.ClearAllFilters

    pt.PivotFields("Employee Name").CurrentPage = Employee
    pt.PivotFields("Dates").PivotFilters.Add Type:=xlCaptionIsBetween, Value1:=SDate, Value2:=EDate


    End Sub

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Filter Pivot Table date column by labels using data in other cells

    Replace xlCaptionIsBetween with xlDateBetween
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    04-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Filter Pivot Table date column by labels using data in other cells

    Thanks for the suggestion. I had tried that, however, I get the following error message:

    The date you entered is not a valid date. Please try again.

    for this line: pt.PivotFields("Dates").PivotFilters.Add Type:=xlDateBetween, Value1:=SDate, Value2:=EDate

    When I change the Sdate and Edate declarations to be Dates rather than Strings, then I get a type mismatch on the EDate = Range("D5")
    The value in D5 is definitely a date. I have used the DateValue function in cell D5.


    When I try to filter the pivot table manually, I have only the option of a value filter for the field in the Values area, or a Label filter for the Dates field. There is no Date filter option available. The dates in the data source are coming from a Access query as a serial number.

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Filter Pivot Table date column by labels using data in other cells

    Attach a workbook, with (anonymized) sample data...

  5. #5
    Registered User
    Join Date
    04-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Filter Pivot Table date column by labels using data in other cells

    I have attached a copy of the workbook. The code is on the "Show Details" button and the Calendar sheet.
    Attached Files Attached Files

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Filter Pivot Table date column by labels using data in other cells

    It's always hard to deal with conflicting locale Date settings...! I wish the world would agree on one standard date format.

    I think I've sorted it though - see attachment.

    You needed to qualify the references to the Start and End date ranges... and convert them to a number to apply the xlCaptionIsBetween filter.

    If this still doesn't work, let me know, and I'll play with it tomorrow on a test machine, where I can change my regional date settings to match yours.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Filter Pivot Table date column by labels using data in other cells

    I think that does it.

    Thanks so much, Olly!

  8. #8
    Registered User
    Join Date
    05-20-2015
    Location
    Toronto,Canada
    MS-Off Ver
    2010
    Posts
    1

    Re: Filter Pivot Table date column by labels using data in other cells

    Hi All,

    I had a similar problem earlier but your code will help me solve it. The only difference I have is that my pivottable can only take dates in specific format like (2015-05-20) otherwise it will not recognize that as a date.

    I am using cells H1 and H2 as SDate and EDate, both cells are already formatted to (yyyy-mm-dd) but when I use the VBA code it doesn't work because it puts it in the filter as (2015/5/20)

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filter Pivot table based on max date in column
    By dpelizzari in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-22-2015, 03:30 AM
  2. Pivot Table - Show Row labels horizontally (Not as Column labels)
    By Catsup in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-16-2014, 01:15 PM
  3. vba code to filter pivot table column labels based on cell vaule
    By nailler167 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2013, 09:12 PM
  4. Replies: 0
    Last Post: 07-03-2012, 11:09 AM
  5. Replies: 6
    Last Post: 03-19-2012, 04:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1