+ Reply to Thread
Results 1 to 2 of 2

Select Multiple Dates In Report Filter Pivot Table But Dates Are Not Static

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    2

    Select Multiple Dates In Report Filter Pivot Table But Dates Are Not Static

    Hello,

    First post so go easy on me haha but here's my situation.
    -
    Every week I have to download a data set that has dates associated 3 weeks of data, but is done every Friday so it'll change dates each week (5/23/2014 - 6/06/2014 and then next week it will be 5/30/2014 - 6/13/2014).

    I have the tables refreshing and updating automatically by recording a macro, but I cannot seem to get it to select the new dates because they are changing every week. One Code I found from another thread demonstrated how to change only 1 value based on a name range, but I was wondering how I could change multiple (7 ideally).

    This is the code:

    Sub Test()

    Dim pt As PivotTable
    Set pt = Worksheets("Pivot").PivotTables("PivotTable1")

    pt.PivotFields("Date").ClearAllFilters
    pt.PivotFields("Date").CurrentPage = Range("Date1").Value

    End Sub

    Thank you for your help!!

  2. #2
    Registered User
    Join Date
    06-06-2014
    Posts
    2

    Re: Select Multiple Dates In Report Filter Pivot Table But Dates Are Not Static

    Ok so I found a code that ALMOST works, problem is idk the syntax for changing the report filter, because as of right now it will only work if the date is part of the Rows or Columns, and not the report filter

    Sub PivotTableFilter4()

    Dim PvtTbl As PivotTable
    Set PvtTbl = Worksheets("Pivot").PivotTables("PivotTable1")

    'delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
    PvtTbl.ClearAllFilters

    PvtTbl.PivotFields("Dates").PivotFilters.Add Type:=xlDateBetween, Value1:="3/14/2014", Value2:="3/19/2014"

    End Sub

    I believe it has something to do with "PivotFilters" portion but I am not sure...

    Thank you!

+ 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. Why my Pivot Table Filter shows individual dates
    By chico.corrales in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-28-2016, 05:52 AM
  2. Replies: 8
    Last Post: 06-24-2014, 03:35 PM
  3. Filter Pivot Table between dates referenced in cell
    By tkleypas13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2013, 04:42 PM
  4. Replies: 0
    Last Post: 05-18-2013, 03:29 AM
  5. Auto update pivot table filter dates
    By kaitco in forum Excel General
    Replies: 5
    Last Post: 11-19-2010, 01:40 PM

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