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!

 

[johnnyL]

You want the column that had the data copied into it to be unhidden?

 

[Ironman]

Yes please, that would be brilliant.

 

[johnnyL]

Change the following line:

            If c.Value = Year(Date) Then

to the following:

            If c.Value = Year(Date) Then
                Columns(c.Column).EntireColumn.Hidden = False

 

[Ironman]

Brilliant, thank you so much for your help Johnny!

(and to you too Michael for your input)

 

[johnnyL]

Glad we could help @Ironman.

 

[Ironman]

Just a quick question while you're still there Johnny - I'm going to adapt the above for a similar 'monthly tracking' worksheet which will also run on workbook_open event on Jan 1 annually. I've renamed the 'dims' as follows:

Dim ColumnMinus1LetterMth  As String
    Dim ColumnLetterMth    As String

but I don't know if I needed to. Also, am I able/do I need to rename Dim c As Variant?

Thanks again!

 

[johnnyL]

If they are in separate sub routines, you don't have to rename them.

You are welcome to rename them to almost anything you want, I would suggest if you do rename them, rename them one at a time and do all of the renaming for the rest of the subroutine places that have that name that you are renaming.

 

[Ironman]

Sorry, can I just confirm that if it's in the same Workbook_Open event, under the one you created for 'Weekly Tracking' to run on Jan 1 then I will need to rename them?

 

[johnnyL]

You don't have to rename them in the 'Dim' section. You can reuse them. You may have to just change what they are equal to in the code that you are adding.

For example You could have
Dim FavoriteFlavor as String

Then in one portion of the subroutine you have
FavoriteFlavor = "Chocolate"

Then farther down in the code you could have
FavoriteFlavor = "Vanilla"

There is no reason to
Dim FavoriteFlavor2 as String

FavoriteFlavor2 = "Vanilla"

In other words you can use the same Dim, just change what you want it to equal as needed.

Hope that helps.

 

[Michael M]

also Keep in mind that you will need to have one workbook_open event for all actions, not multiple workbook_open events.
Otherwise you will get an "Ambiguous Name detected" error