Macro to Filter Pivot Table on Dates

mib1019

Board Regular
Joined
Nov 9, 2017
Messages
55
Office Version
  1. 365
  2. 2019
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mib1019

Board Regular
Joined
Nov 9, 2017
Messages
55
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,495
Latest member
Ragamacam

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
Top