How to get pivot table to adjust filter automatically by week

phillipc1

New Member
Joined
Oct 11, 2023
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a couple of pivot tables that are giving me a rolling 12 week look at vehicle incidents by different categories. As this is a rolling 12 week pivot table, we would like to try to automate this more than just filtering the new column and removing the old column every week for each pivot table. Is this something that can be automated or at least changed to where I do not have to manually adjust every pivot table each week? I have the XL2BB below for some of the pivot tables to show what I am working with.

For example, the latest week on the tables is 11/27-12/3, but when I want to update this on Monday I would like for a new column to be at the end showing 12/4-12/10 with the first column 9/11-9/17 falling off.

NEW VIR PROJECT.xlsx
ABCDEFGHIJKLMN
3COMPANY VEHICLE ACTION
4Count of Vehicle EventsEvent Date
5Company Vehicle Action9/11/2023 - 9/17/20239/18/2023 - 9/24/20239/25/2023 - 10/1/202310/2/2023 - 10/8/202310/9/2023 - 10/15/202310/16/2023 - 10/22/202310/23/2023 - 10/29/202310/30/2023 - 11/5/202311/6/2023 - 11/12/202311/13/2023 - 11/19/202311/20/2023 - 11/26/202311/27/2023 - 12/3/2023Grand Total
6Backing/Reversing25113525461540
7Forward Motion232423441227
8Turning312231221320
9Rear Ending3122153219
10Struck Fixed Object313112415
11(blank)3211221315
12Struck Overhead Object22111119
13Side Swipe/Passing11111319
14Backing/Reversing Active Roadway121116
15Lane Change11114
16Failed to Yield (light, stop sign)1113
17Stopped (Not Parked)213
18Trailer Separation11
19Merging11
20Grand Total15121215111711171523717172
Pivot Tables
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N6:N19Other TypeColor scaleNO
B19:M19Other TypeColor scaleNO
B18:M18Other TypeColor scaleNO
B17:M17Other TypeColor scaleNO
B16:M16Other TypeColor scaleNO
B15:M15Other TypeColor scaleNO
B14:M14Other TypeColor scaleNO
B13:M13Other TypeColor scaleNO
B12:M12Other TypeColor scaleNO
B11:M11Other TypeColor scaleNO
B10:M10Other TypeColor scaleNO
B9:M9Other TypeColor scaleNO
B8:M8Other TypeColor scaleNO
B7:M7Other TypeColor scaleNO
B6:M6Other TypeColor scaleNO
B20:M20Other TypeColor scaleNO


NEW VIR PROJECT.xlsx
ABCDEFGHIJKLMN
80VEHICLE CLASS
81Count of Vehicle EventsEvent Date
82Vehicle Class9/11/2023 - 9/17/20239/18/2023 - 9/24/20239/25/2023 - 10/1/202310/2/2023 - 10/8/202310/9/2023 - 10/15/202310/16/2023 - 10/22/202310/23/2023 - 10/29/202310/30/2023 - 11/5/202311/6/2023 - 11/12/202311/13/2023 - 11/19/202311/20/2023 - 11/26/202311/27/2023 - 12/3/2023Grand Total
83Class A4354936962556
84Light47253462103652
85Class C543261222128
86Class B2141113231221
87(blank)3211221315
88Grand Total15121215111711171523717172
Pivot Tables
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N83:N87Other TypeColor scaleNO
B87:M87Other TypeColor scaleNO
B86:M86Other TypeColor scaleNO
B85:M85Other TypeColor scaleNO
B84:M84Other TypeColor scaleNO
B83:M83Other TypeColor scaleNO


NEW VIR PROJECT.xlsx
ABCDEFGHIJKLMN
122TENURE AT TIME INCIDENT
123Count of Vehicle EventsEvent Date
124Tenure9/11/2023 - 9/17/20239/18/2023 - 9/24/20239/25/2023 - 10/1/202310/2/2023 - 10/8/202310/9/2023 - 10/15/202310/16/2023 - 10/22/202310/23/2023 - 10/29/202310/30/2023 - 11/5/202311/6/2023 - 11/12/202311/13/2023 - 11/19/202311/20/2023 - 11/26/202311/27/2023 - 12/3/2023Grand Total
1250-2844774210792771
1263-5132253445130
1276-8322132241424
1289-1111114
12912-143111118
13015-17112
13118-201214
13221-231113
13324-2611
134(blank)3133122232325
135Grand Total15121215111711171523717172
Pivot Tables
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N125:N134Other TypeColor scaleNO
B135:M135Other TypeColor scaleNO
B133:M133Other TypeColor scaleNO
B130:M130Other TypeColor scaleNO
B132:M132Other TypeColor scaleNO
B128:M128Other TypeColor scaleNO
B131:M131Other TypeColor scaleNO
B129:M129Other TypeColor scaleNO
B134:M134Other TypeColor scaleNO
B127:M127Other TypeColor scaleNO
B126:M126Other TypeColor scaleNO
B125:M125Other TypeColor scaleNO
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi,

You can try this code, you just need to update to match your workbook.

You can then attach the macro to a button on the sheet or set it to run when the workbook is opened.

As always test on a copy and not the live document.

VBA Code:
Sub RefreshPivotTableDateRange()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date

    ' Set your Pivot Table variable
    Set pt = Worksheets("Sheet1").PivotTables("PivotTable1") 
' Change "Sheet1" and "PivotTable1" to your sheet and Pivot Table names

    ' Get the current date
    currentDate = Date

    ' Calculate start and end dates
    startDate = currentDate - 7 ' One week back
    endDate = currentDate + 14 ' Two weeks forward

    ' Set the Pivot Field representing the date range
    Set pf = pt.PivotFields("Date") 
' Change "Date" to the name of your date field in the Pivot Table

    ' Filter the Pivot Field for the desired date range
    pf.ClearAllFilters
    pf.PivotFilters.Add Type:=xlDateBetween, Value1:=startDate, Value2:=endDate
End Sub
 
Upvote 0
Hi, do you have a sample of the data where this pivot table get their data from?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

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