Help automatically refreshing a year using VBA

**zayas23** · 04-15-2020, 11:03 AM

Hi all,

I was hoping to get some help with this problem I'm having. I'm creating a training history spreadsheet for employees. I have the current year and the 2 previous years, so 2018, 2019, and 2020. Using VBA code, I'd like to automatically update these 3 years at the end of each year (when the date is 12/31/2020, 12/31/2021, etc. it updates the 3 years) so that its the current year and 2 previous. For example, 2018, 2019, 2020 would become 2019, 2020, 2021 and so on and so forth when the date of the current year is past 12/31/20XY. I'm trying to do this using if statements but am struggling with making it a loop and getting it to run every time the workbook is opened. I'm wondering if I should use while statements instead? Much of my trouble comes from only wanting to display the year for these columns but trying to compare it with a full date (2018 vs. 12/31/2020) and the fact that the starting years are in the past so I'd have to write some additional code to make up for this and get it on the right track starting 2021.

I'm currently practicing different blurbs of code on a random spread sheet right now to see line by line if its working or not and seeing what I need to adjust but I've run into a wall thought wise. I could just be overthinking it though. Anything helps!

I've attached my code below, I've also tried other ways but this is the most recent one I've been working on.

Please Login or Register to view this content.

**Pepe Le Mokko** · 04-15-2020, 11:38 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I did it for you this time. Please read forum rules. Thanks

**zayas23** · 06-02-2020, 08:15 AM

Pepe,

Thought I had done this properly, so sorry. Thanks!

**LJMetzger** · 06-21-2020, 02:20 PM

Hi zayas23 as Welcome to ExcelForum,

I think you are making things more complicated than they have to be. The only thing we really need for comparison is the 'current year'.

Try the following code contained in the attached sample workbook. It also creates a New Worksheet Tab for each 'new year.'

Please Login or Register  to view this content.

Here are a couple of tips which may help you in the future:
To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

To manually set a breakpoint, see http://www.wiseowl.co.uk/blog/s196/breakpoints.htm

Lewis