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:

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,842
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You want the column that had the data copied into it to be unhidden?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Yes please, that would be brilliant.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,842
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Change the following line:

VBA Code:
            If c.Value = Year(Date) Then

to the following:

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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Brilliant, thank you so much for your help Johnny!

(and to you too Michael for your input)
 

johnnyL

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

ADVERTISEMENT

Glad we could help @Ironman.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

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

ADVERTISEMENT

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

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
1,842
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,818
Messages
5,743,382
Members
423,792
Latest member
travisds

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