bbqsmokeman
New Member
Have workbook with approx 30 sheets each have person's name and 2 calendar years (2016 at range L13:AH48) and (2015 at range AJ13:BF48) and Summary has same calendars at M8:AI43 -2016 calendar and 2015 calendar at AK8:BG43. I have a calendar sheet created with 12 month calendars till 2021 for now (examples- 2019 calendar at AX3:BT38; 2018 calendar at BV3:CR38) the newer calendars are closer to A8 range and older calendars are further into the worksheet. I built code to move 2016 and 2015 calendars over and delete 2015 calendar but am unable to figure out how to have the macro find calendar 2017 on calendar sheet and place it where the 2016 was. End result is to have 2 calendars each sheet (one current year and the other previous year). I built a macro to accomplish the move, delete and restore legend but I am left with a blank range that I want to add this years calendar into. I have on each sheet cell F1 with =today() and would like to know if the macro can change every sheet when next year arrives or does it have to be done manually on each sheet. I have a summary sheet also that has the same 2 calendars but at slightly different locations - 2016 calendar at M8: AI43 and 2015 calendar at AK8: BG43.
Here is the code I made to move the calendars over (not coded for Summary since has different ranges) but have code working on one reps worksheet except unable to figure out code to find the calendar for year we are in and place in the empty range). The code I have works great at this point but is there anything easier that can look at all sheets regardless of location and when next year arrives click a button and all sheets calendars move and delete the older calendar and keep current and last year calendar.
Here is the code I made to move the calendars over (not coded for Summary since has different ranges) but have code working on one reps worksheet except unable to figure out code to find the calendar for year we are in and place in the empty range). The code I have works great at this point but is there anything easier that can look at all sheets regardless of location and when next year arrives click a button and all sheets calendars move and delete the older calendar and keep current and last year calendar.
Code:
Sub TeamSliderAndClean()
Columns("L:AI").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 2.29
Range("AJ1:BH6").Select
Selection.Cut
Range("L1").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
Columns("BH:CD").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
Range("L1").Select
End Sub