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.

Sub test() ' This is an Excel VBA code designed to automatically update the workbook ' and display the most recent year and 2 years prior for trainings ' This code will automatically run upon opening the workbook ' This code will update the years after December 31st 20XX, therefore ' for each new year, information from the 3rd previous year ' should be recorded for future use so as not to lose any ' training histories '------------------------------------------------------------------------ ' Set initial year values for overall spreadsheet in first 3 columns Dim Y1 As Variant Y1 = 2018 Range("c10").Value = Y1 Range("d10").Value = Y1 + 1 Range("e10").Value = Y1 + 2 ' Set current date to display in mm/dd/yyyy format Dim currentdate As Date currentdate = Date Range("h10").Value = currentdate ' Set initial end of year date to display in mm/dd/yyyy format Dim endofyear As Date endofyear = CDate("12/31/2020") Range("i10").Value = endofyear Dim endofyear2 As Date endofyear2 = DateSerial(2020, 12, 31) Range("f15").Value = endofyear2 ' This portion of the code is an if statement to determine whether or not ' the current date has past the end of year date ' If it has, the initial year values will all update by 1 or 2 year(s), if ' not they will remain the same If currentdate < endofyear2 Then Range("c10").Value = Range("c10").Value Range("d10").Value = Range("c10").Value + 1 Range("e10").Value = Range("c10").Value + 2 Else: Range("c10").Value = Range("c10").Value + 1 Range("d10").Value = Range("c10").Value + 1 Range("e10").Value = Range("c10").Value + 2 End If End Sub

**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.'

Option Explicit

Sub SeedData()
  'This creates Initial Conditions to simulate an Existing Workbook

  Dim iSeedYear As Long
  Dim iYear As Long
  
  Dim sYear As String
  
  Dim bSheetExists As Boolean
  
  iSeedYear = 2015

  'Put Seed Years in the Spreadsheet
  ThisWorkbook.Sheets("Sheet1").Range("C10") = iSeedYear
  ThisWorkbook.Sheets("Sheet1").Range("D10") = iSeedYear + 1
  ThisWorkbook.Sheets("Sheet1").Range("E10") = iSeedYear + 2

  'Create Data Tabs if they Don't Exist
  For iYear = iSeedYear To iSeedYear + 2
  
    'Convert the Numerical Year to text
    'Determine if the Sheet Exists
    'If the Sheet Does NOT EXIST, add a new Sheet after 'Sheet1'
    sYear = CStr(iYear)
    bSheetExists = LjmSheetExists(sYear)
    If bSheetExists = False Then
      ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets("Sheet1")).Name = sYear
    End If
    
  Next iYear
  
  'Put the Focus On 'Sheet1'
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
  ThisWorkbook.Sheets("Sheet1").Select
  
  'Display a Completion Message
  MsgBox "SeedData() completed based on Base Year " & iSeedYear & "."

End Sub

Sub SimulateWorkbookOpenCode()

  Dim iCurrentYear As Long
  Dim iLatestYearInSpreadsheetList As Long
  Dim iSeedYear As Long
  Dim iYear As Long
  
  Dim sYear As String
  
  Dim bSheetExists As Boolean
  
  'Get the Current Year
  iCurrentYear = Year(Date)
  
  'Get the Latest Year in the Spreadsheet List
  iLatestYearInSpreadsheetList = ThisWorkbook.Sheets("Sheet1").Range("E10").Value
  
  'Update the Spreadsheet if the 'Current Year' is less than the 'Latest Year in the Spreadsheet List'
  If iLatestYearInSpreadsheetList < iCurrentYear Then
  
    'Create a New Seed Year
    iSeedYear = iLatestYearInSpreadsheetList - 1
  
    'Put Seed Years in the Spreadsheet
    ThisWorkbook.Sheets("Sheet1").Range("C10").Value = iSeedYear
    ThisWorkbook.Sheets("Sheet1").Range("D10").Value = iSeedYear + 1
    ThisWorkbook.Sheets("Sheet1").Range("E10").Value = iSeedYear + 2

    'Create Data Tabs if they Don't Exist
    For iYear = iSeedYear To iSeedYear + 2
  
      'Convert the Numerical Year to text
      'Determine if the Sheet Exists
      'If the Sheet Does NOT EXIST, add a new Sheet after 'Sheet1'
      sYear = CStr(iYear)
      bSheetExists = LjmSheetExists(sYear)
      If bSheetExists = False Then
        ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets("Sheet1")).Name = sYear
      End If
    
    Next iYear
    
    'Get the NEW 'Latest Year in the Spreadsheet List'
    iLatestYearInSpreadsheetList = ThisWorkbook.Sheets("Sheet1").Range("E10").Value
    
    'Put the Focus On 'Sheet1'
    Application.ScreenUpdating = True
    ThisWorkbook.Activate
    ThisWorkbook.Sheets("Sheet1").Select
  
    'Display a Completion Message
    If iLatestYearInSpreadsheetList < iCurrentYear Then
      MsgBox "SimulateWorkbookOpenCode() updated Years." & vbCrLf & _
             "CURRENT CONFIGURATION is still 'Out of Date'." & vbCrLf & _
             "Save and Close this File." & vbCrLf & _
             "Repeat as required until this file is 'Up to Date'."
    Else
      MsgBox "SimulateWorkbookOpenCode() updated Years" & vbCrLf & _
             "to CURRENT CONFIGURATION."
    End If
  
  
  End If

End Sub

Private Function LjmSheetExists(SheetName As String) As Boolean
'Return value TRUE if sheet exists

  On Error Resume Next

  If Sheets(SheetName) Is Nothing Then
    LjmSheetExists = False
  Else
    LjmSheetExists = True
  End If
  On Error GoTo 0
  
End Function

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