How to get pivot table to adjust filter automatically by week

[phillipc1]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	COMPANY VEHICLE ACTION
4	Count of Vehicle Events	Event Date
5	Company Vehicle Action	9/11/2023 - 9/17/2023	9/18/2023 - 9/24/2023	9/25/2023 - 10/1/2023	10/2/2023 - 10/8/2023	10/9/2023 - 10/15/2023	10/16/2023 - 10/22/2023	10/23/2023 - 10/29/2023	10/30/2023 - 11/5/2023	11/6/2023 - 11/12/2023	11/13/2023 - 11/19/2023	11/20/2023 - 11/26/2023	11/27/2023 - 12/3/2023	Grand Total
6	Backing/Reversing	2	5	1	1	3	5	2	5	4	6	1	5	40
7	Forward Motion	2			3	2	4	2	3	4	4	1	2	27
8	Turning	3	1	2	2	3	1			2	2	1	3	20
9	Rear Ending	3	1		2		2	1	5		3		2	19
10	Struck Fixed Object		3	1	3	1		1	2	4				15
11	(blank)			3	2	1	1	2			2	1	3	15
12	Struck Overhead Object	2	2	1			1	1	1		1			9
13	Side Swipe/Passing				1	1	1		1	1	3	1		9
14	Backing/Reversing Active Roadway	1		2			1					1	1	6
15	Lane Change						1	1			1	1		4
16	Failed to Yield (light, stop sign)			1							1		1	3
17	Stopped (Not Parked)	2			1									3
18	Trailer Separation			1										1
19	Merging							1						1
20	Grand Total	15	12	12	15	11	17	11	17	15	23	7	17	172
Pivot Tables

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:N19	Other Type	Color scale		NO
B19:M19	Other Type	Color scale		NO
B18:M18	Other Type	Color scale		NO
B17:M17	Other Type	Color scale		NO
B16:M16	Other Type	Color scale		NO
B15:M15	Other Type	Color scale		NO
B14:M14	Other Type	Color scale		NO
B13:M13	Other Type	Color scale		NO
B12:M12	Other Type	Color scale		NO
B11:M11	Other Type	Color scale		NO
B10:M10	Other Type	Color scale		NO
B9:M9	Other Type	Color scale		NO
B8:M8	Other Type	Color scale		NO
B7:M7	Other Type	Color scale		NO
B6:M6	Other Type	Color scale		NO
B20:M20	Other Type	Color scale		NO

NEW VIR PROJECT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
80	VEHICLE CLASS
81	Count of Vehicle Events	Event Date
82	Vehicle Class	9/11/2023 - 9/17/2023	9/18/2023 - 9/24/2023	9/25/2023 - 10/1/2023	10/2/2023 - 10/8/2023	10/9/2023 - 10/15/2023	10/16/2023 - 10/22/2023	10/23/2023 - 10/29/2023	10/30/2023 - 11/5/2023	11/6/2023 - 11/12/2023	11/13/2023 - 11/19/2023	11/20/2023 - 11/26/2023	11/27/2023 - 12/3/2023	Grand Total
83	Class A	4		3	5	4	9	3	6	9	6	2	5	56
84	Light	4	7	2	5	3		4	6	2	10	3	6	52
85	Class C	5	4		3	2	6	1	2	2	2		1	28
86	Class B	2	1	4		1	1	1	3	2	3	1	2	21
87	(blank)			3	2	1	1	2			2	1	3	15
88	Grand Total	15	12	12	15	11	17	11	17	15	23	7	17	172
Pivot Tables

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N83:N87	Other Type	Color scale		NO
B87:M87	Other Type	Color scale		NO
B86:M86	Other Type	Color scale		NO
B85:M85	Other Type	Color scale		NO
B84:M84	Other Type	Color scale		NO
B83:M83	Other Type	Color scale		NO

NEW VIR PROJECT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
122	TENURE AT TIME INCIDENT
123	Count of Vehicle Events	Event Date
124	Tenure	9/11/2023 - 9/17/2023	9/18/2023 - 9/24/2023	9/25/2023 - 10/1/2023	10/2/2023 - 10/8/2023	10/9/2023 - 10/15/2023	10/16/2023 - 10/22/2023	10/23/2023 - 10/29/2023	10/30/2023 - 11/5/2023	11/6/2023 - 11/12/2023	11/13/2023 - 11/19/2023	11/20/2023 - 11/26/2023	11/27/2023 - 12/3/2023	Grand Total
125	0-2	8	4	4	7	7	4	2	10	7	9	2	7	71
126	3-5		1	3	2	2	5	3	4	4	5	1		30
127	6-8	3	2		2	1	3	2		2	4	1	4	24
128	9-11						1			1		1	1	4
129	12-14		3	1	1			1			1		1	8
130	15-17								1		1			2
131	18-20			1			2			1				4
132	21-23		1					1					1	3
133	24-26	1												1
134	(blank)	3	1	3	3	1	2	2	2		3	2	3	25
135	Grand Total	15	12	12	15	11	17	11	17	15	23	7	17	172
Pivot Tables

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N125:N134	Other Type	Color scale		NO
B135:M135	Other Type	Color scale		NO
B133:M133	Other Type	Color scale		NO
B130:M130	Other Type	Color scale		NO
B132:M132	Other Type	Color scale		NO
B128:M128	Other Type	Color scale		NO
B131:M131	Other Type	Color scale		NO
B129:M129	Other Type	Color scale		NO
B134:M134	Other Type	Color scale		NO
B127:M127	Other Type	Color scale		NO
B126:M126	Other Type	Color scale		NO
B125:M125	Other Type	Color scale		NO

 

[t0ny84]

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.

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

 

[felixstraube]

Hi, do you have a sample of the data where this pivot table get their data from?