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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
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:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Or just use a standard sub and step through using F8
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,846
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
VBA Code:
DateSerial(Year(Now), 1, 1) + 365

That would = 01/01 of the following year. Is that what you wanted?
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Hi Johnny, I need it to run on the first day of the new year, assuming I remember to open the workbook on Jan 1.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
Sub Worksheet_open()
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
Range(Cells(317, c - 1), Cells(369, c)).Copy Cells(317, c)
Exit Sub
End If
Next c
End If
End Sub
It didn't error but it didn't fill any cells either.

Also, will the code run next year if the column is hidden? Would it be possible to add a line to the code so the new year column is unhidden please?

Thanks again!
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,846
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

You may want to schedule an 'OnTime' event then.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
You may want to schedule an 'OnTime' event then.
If it was supposed to run when I opened it today, what difference would an 'OnTime' event make?

Edit - or were you referring to the possibility of me forgetting? :)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Was Sheets("Weekly Tracking") the active sheet ?
Did you put in a Breakpoint to ensure the code fired ?
Did you step through the code using F8 ?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,846
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If it was supposed to run when I opened it today, what difference would an 'OnTime' event make?

Edit - or were you referring to the possibility of me forgetting? :)
Sorry, OnTimewill not do it. I guess you would have to do a

Sub Auto_Open() 'runs whenever workbook is opened
and then check for the date, if date = what you want then execute desired code.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hi @johnnyL
That's what this code does in post #8
VBA Code:
If Date = DateSerial(Year(Now), 1, 1) Then
But I would run it without that until we know the code runs properly
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,846
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
My apologies @Michael M. I got side tracked on the 'OnTime', which only does Time, not date. :rolleyes:

It does look like Autoopen option could be a better option as it doesn't appear to have to be placed in a specific location.
 

Forum statistics

Threads
1,148,020
Messages
5,744,361
Members
423,864
Latest member
GolfingTitan116

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
Top