Use VBA to automate dragging column range from left to right on Jan 1

[Ironman]

Hi

The below sheet extract shows weekly stats by year, which feeds a chart.

Every year I need to update the data from the previous year column by simply dragging and dropping it to the current year column but I always forget to do this and wonder why the chart doesn't seem to be working.

Book1
	AJ	AK	AL
316	WEEK	2020	2021
317	1	33	0
318	2	21	0
319	3	19	0
320	4	22	0
321	5	22	0
322	6	29	0
323	7	19	0
324	8	32	0
325	9	28	0
326	10	34	0
327	11	23	0
328	12	6	0
329	13	6	0
330	14	0	0
331	15	5	0
332	16	0	0
333	17	0	0
334	18	4	0
335	19	0	0
336	20	0	0
337	21	0	0
338	22	14	0
339	23	18	8
340	24	4	6
341	25	0	19
342	26	0	23
343	27	0	6
344	28	0	18
345	29	0	19
346	30	0	21
347	31	0	15
348	32	0	22
349	33	0	22
350	34	0	26
351	35	0	11
352	36	0	15
353	37	0
354	38	0
355	39	0
356	40	0
357	41	2
358	42	6
359	43	0
360	44	8
361	45	4
362	46	0
363	47	0
364	48	0
365	49	0
366	50	0
367	51	0
368	52	0
369	TOTS	360	231
Weekly Tracking

Cell Formulas
Range	Formula
AK317:AL317	AK317	=AK2
AK318:AL318	AK318	=AK7
AK319:AL319	AK319	=AK12
AK320:AL320	AK320	=AK17
AK321:AL321	AK321	=AK22
AK322:AL322	AK322	=AK27
AK323:AL323	AK323	=AK32
AK324:AL324	AK324	=AK37
AK325:AL325	AK325	=AK42
AK326:AL326	AK326	=AK47
AK327:AL327	AK327	=AK52
AK328:AL328	AK328	=AK57
AK329:AL329	AK329	=AK62
AK330:AL330	AK330	=AK67
AK331:AL331	AK331	=AK72
AK332:AL332	AK332	=AK77
AK333:AL333	AK333	=AK82
AK334:AL334	AK334	=AK87
AK335:AL335	AK335	=AK92
AK336:AL336	AK336	=AK97
AK337:AL337	AK337	=AK102
AK338:AL338	AK338	=AK107
AK339:AL339	AK339	=AK112
AK340:AL340	AK340	=AK117
AK341:AL341	AK341	=AK122
AK342:AL342	AK342	=AK127
AK343:AL343	AK343	=AK132
AK344:AL344	AK344	=AK137
AK345:AL345	AK345	=AK142
AK346:AL346	AK346	=AK147
AK347:AL347	AK347	=AK152
AK348:AL348	AK348	=AK157
AK349:AL349	AK349	=AK162
AK350:AL350	AK350	=AK167
AK351:AL351	AK351	=AK172
AK352:AL352	AK352	=AK177
AK353:AL353	AK353	=AK182
AK354:AL354	AK354	=AK187
AK355:AL355	AK355	=AK192
AK356:AL356	AK356	=AK197
AK357:AL357	AK357	=AK202
AK358:AL358	AK358	=AK207
AK359:AL359	AK359	=AK212
AK360:AL360	AK360	=AK217
AK361:AL361	AK361	=AK222
AK362:AL362	AK362	=AK227
AK363:AL363	AK363	=AK232
AK364:AL364	AK364	=AK237
AK365:AL365	AK365	=AK242
AK366:AL366	AK366	=AK247
AK367:AL367	AK367	=AK252
AK368:AL368	AK368	=AK257
AK369:AL369	AK369	=SUM(AK317:AK368)

Named Ranges
Name	Refers To	Cells
WeeklyTrackingLogYearSeries	=INDEX('Weekly Tracking'!$AB$317:$CA$368,0,WeeklyTrackingColumn-COLUMN(WeeklyTrackingXAxis))	AL369
WeeklyTrackingPreviousYearSeries	=INDEX('Weekly Tracking'!$AB$317:$CA$368,0,WeeklyTrackingColumn-COLUMN(WeeklyTrackingXAxis)-1)	AK369

Cells with Data Validation
Cell	Allow	Criteria
AK316:AL316	Any value

What I need is on Jan 1 each year for the data from the last filled column (for 2021 it's AL317:369) to "auto-drag" to the next column (AM317:369 for 2022) and each year thereafter, without me having to remember to do this myself.

I don't know if this makes a difference but future years are hidden and I have to manually unhide them to manually drag the previous column cells across. Could the solution also unhide the next year's column as well?

Many thanks!

 

[Ironman]

Is anyone able to assist please?

Thank you!

 

[Michael M]

which sheet is this on ??

 

[Ironman]

Weekly Tracking AA317:BZ317

 

[Ironman]

Bump

 

[Michael M]

Didn't you have a first day of the year code recently ??
Do you still have that code...please post it.

 

[Ironman]

Private Sub Workbook_Open()
If Date = DateSerial(Year(Now), 1, 1) Then

 

[Michael M]

UNTESTED

Sub MM1()
Dim c As Variant
If Date = DateSerial(Year(Now), 1, 1) Then
For Each c In Range("AB316:BZ316")
If c.Value = Year(Date) Then
Range(Cells(317, c - 1), Cells(369, c)).Copy Cells(317, c)
Exit Sub
End If
Next c
End If
End Sub

 

[Ironman]

Hey thanks ever so much Michael!

Just trying to test this on a test sheet. Would this be better triggered as a workbook_open event, where I guess I'd need to amend to this

For Each c In Sheets("Weekly Tracking").Range (AB316:BZ316)

or worksheet_activate event?

 

[Michael M]

WorkBook_open()