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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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
AJAKAL
316WEEK20202021
3171330
3182210
3193190
3204220
3215220
3226290
3237190
3248320
3259280
32610340
32711230
3281260
3291360
3301400
3311550
3321600
3331700
3341840
3351900
3362000
3372100
33822140
33923188
3402446
34125019
34226023
3432706
34428018
34529019
34630021
34731015
34832022
34933022
35034026
35135011
35236015
353370 
354380 
355390 
356400 
357412 
358426 
359430 
360448 
361454 
362460 
363470 
364480 
365490 
366500 
367510 
368520 
369TOTS360231
Weekly Tracking
Cell Formulas
RangeFormula
AK317:AL317AK317=AK2
AK318:AL318AK318=AK7
AK319:AL319AK319=AK12
AK320:AL320AK320=AK17
AK321:AL321AK321=AK22
AK322:AL322AK322=AK27
AK323:AL323AK323=AK32
AK324:AL324AK324=AK37
AK325:AL325AK325=AK42
AK326:AL326AK326=AK47
AK327:AL327AK327=AK52
AK328:AL328AK328=AK57
AK329:AL329AK329=AK62
AK330:AL330AK330=AK67
AK331:AL331AK331=AK72
AK332:AL332AK332=AK77
AK333:AL333AK333=AK82
AK334:AL334AK334=AK87
AK335:AL335AK335=AK92
AK336:AL336AK336=AK97
AK337:AL337AK337=AK102
AK338:AL338AK338=AK107
AK339:AL339AK339=AK112
AK340:AL340AK340=AK117
AK341:AL341AK341=AK122
AK342:AL342AK342=AK127
AK343:AL343AK343=AK132
AK344:AL344AK344=AK137
AK345:AL345AK345=AK142
AK346:AL346AK346=AK147
AK347:AL347AK347=AK152
AK348:AL348AK348=AK157
AK349:AL349AK349=AK162
AK350:AL350AK350=AK167
AK351:AL351AK351=AK172
AK352:AL352AK352=AK177
AK353:AL353AK353=AK182
AK354:AL354AK354=AK187
AK355:AL355AK355=AK192
AK356:AL356AK356=AK197
AK357:AL357AK357=AK202
AK358:AL358AK358=AK207
AK359:AL359AK359=AK212
AK360:AL360AK360=AK217
AK361:AL361AK361=AK222
AK362:AL362AK362=AK227
AK363:AL363AK363=AK232
AK364:AL364AK364=AK237
AK365:AL365AK365=AK242
AK366:AL366AK366=AK247
AK367:AL367AK367=AK252
AK368:AL368AK368=AK257
AK369:AL369AK369=SUM(AK317:AK368)
Named Ranges
NameRefers ToCells
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
CellAllowCriteria
AK316:AL316Any 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!
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Is anyone able to assist please?

Thank you!
 
Upvote 0
Didn't you have a first day of the year code recently ??
Do you still have that code...please post it.
 
Upvote 0
VBA Code:
Private Sub Workbook_Open()
If Date = DateSerial(Year(Now), 1, 1) Then
 
Upvote 0
UNTESTED
VBA Code:
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
 
Upvote 0
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
VBA Code:
For Each c In Sheets("Weekly Tracking").Range (AB316:BZ316)
or worksheet_activate event?
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

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