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]

Hi Michael

Was Sheets("Weekly Tracking") the active sheet ?

Yes

Did you put in a Breakpoint to ensure the code fired ?

Yes

Did you step through the code using F8 ?

Yes, I've just done that and it skips this line

Range(Cells(317, c - 1), Cells(369, c)).Copy Cells(317, c)

and goes to the first End If

 

[Ironman]

Is the code written to increase the formula by 1 column in rows 317:369 e.g. for 2021 AL317 should have the value "=AL2", AL318 =AL7, AL319 =AL12 etc and for 2022 I would expect AM317 to show "=AM2", AM318 =AM7, AM319 =AM12 etc?

 

[johnnyL]

Hi

I've just tested it by clearing the data for the current year and inserting today's date to run it as a worksheet_open event as below
...
It didn't error but it didn't fill any cells either.

was the date that you put in 9/15/2021?

 

[Ironman]

was the date that you put in 9/15/2021?

Yes that's today's date in the UK where I am

 

[johnnyL]

Ok see if this does what you want:

Sub test2()
'
    Dim ColumnMinus1Letter  As String
    Dim ColumnLetter        As String
    Dim c                   As Variant
'
    If Date = DateSerial(Year(Now), 9, 15) Then
        For Each c In Sheets("Weekly Tracking").Range("AB316:BZ316")
            If c.Value = Year(Date) Then
                ColumnLetter = Split(Cells(1, c.Column).Address, "$")(1)
                ColumnMinus1Letter = Split(Cells(1, c.Column - 1).Address, "$")(1)
'
                Sheets("Weekly Tracking").Range(ColumnMinus1Letter & "317:" & ColumnMinus1Letter & "369").Copy Sheets("Weekly Tracking").Range(ColumnLetter & "317")
                Exit Sub
            End If
        Next c
    End If
End Sub

When it finds a match for a year, it will copy the previous year data into the column that matched for the year.

 

[Ironman]

Many thanks Johnny - before I test it, should this do what I requested in Post #22 i.e. not "copy" as such, or will Excel know to add a column number automatically?

 

[johnnyL]

The formulas are copied over, for example AK317 has formula of = AK2 .... AL317 will have formula of = AL2

 

[Ironman]

Brilliant! That ran perfectly in a module but when I cleared the new 2021 data then copied the code into a Workbook_Open event as below, closed and re-opened, it didn't run.

Private Sub Worksheet_open()
        Dim ColumnMinus1Letter  As String
    Dim ColumnLetter        As String
    Dim c                   As Variant
'
    If Date = DateSerial(Year(Now), 9, 15) Then
        For Each c In Sheets("Weekly Tracking").Range("AB316:BZ316")
            If c.Value = Year(Date) Then
                ColumnLetter = Split(Cells(1, c.Column).Address, "$")(1)
                ColumnMinus1Letter = Split(Cells(1, c.Column - 1).Address, "$")(1)
'
                Sheets("Weekly Tracking").Range(ColumnMinus1Letter & "317:" & ColumnMinus1Letter & "369").Copy Sheets("Weekly Tracking").Range(ColumnLetter & "317")
                Exit Sub
            End If
        Next c
    End If
End Sub

Also, all future year columns are hidden. Would you be able to add a line to the above unhiding the next year's column please (the columns in my test sheet were hidden and the 2021 data was there but hidden)?

Thanks again!

 

[johnnyL]

Brilliant! That ran perfectly in a module but when I cleared the new 2021 data then copied the code into a Workbook_Open event as below, closed and re-opened, it didn't run.

Private Sub Worksheet_open()                   ' <--- Try Workbook_Open(), not worksheet ;)
        Dim ColumnMinus1Letter  As String
    Dim ColumnLetter        As String
    Dim c                   As Variant
'
    If Date = DateSerial(Year(Now), 9, 15) Then
        For Each c In Sheets("Weekly Tracking").Range("AB316:BZ316")
            If c.Value = Year(Date) Then
                ColumnLetter = Split(Cells(1, c.Column).Address, "$")(1)
                ColumnMinus1Letter = Split(Cells(1, c.Column - 1).Address, "$")(1)
'
                Sheets("Weekly Tracking").Range(ColumnMinus1Letter & "317:" & ColumnMinus1Letter & "369").Copy Sheets("Weekly Tracking").Range(ColumnLetter & "317")
                Exit Sub
            End If
        Next c
    End If
End Sub

Also, all future year columns are hidden. Would you be able to add a line to the above unhiding the next year's column please (the columns in my test sheet were hidden and the 2021 data was there but hidden)?

Thanks again!

 

[Ironman]

Doh, what am I like yes, it works great now, thanks a lot Johnny. Could you tell me how the next year's column can be auto-unhidden as well please?

Thanks again!