Macro to Filter Pivot Table on Dates

mib1019

Board Regular
Joined
Nov 9, 2017
Messages
65
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thank you in advance for help with this.

I want to remove the Slicer from the top of this worksheet (cumbersome) and have the pivot table refreshed/filtered (on Worksheet Activate event) for Wk Ending dates >= this coming Saturday, which would be the 'current' week ending date.

Thanks a million!
MB

1634664934255.png
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Made a little progress. I have a cell on the worksheet that returns the date the previous Saturday:

=TODAY()-MOD(WEEKDAY(TODAY(),1),7)
It's a named range called DateFilter
So its value is 10/16/21; all is good.

My worksheet activate code calls this sub.
VBA Code:
Sub FilterBasedOnDate()

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim d8te As Date
    
    Set ws = ActiveSheet
    d8te = Range("DateFilter").Value
    Debug.Print d8te
    
    For Each pt In ws.PivotTables
        pt.PivotFields("Wk Ending").ClearAllFilters
        pt.PivotFields("Wk Ending").PivotFilters.Add _
            Type:=xlAfterOrEqualTo, Value1:=d8te
    Next pt

End Sub

The result is the pivot table being filtered for Wk Ending 10/23/21 and later.

Hopefully, someone can spot what's wrong here.

Thanks in advance for your help!
MB
 
Upvote 0
Solution

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top