CODEWITHSUNDEEP
excelvba
Derek Harden
Derek Harden

Reputation: 41

Changing Dates in VBA

I am attempting to replace worksheet automatically. Currently I have multiple worksheets with the title "JAN 18", "FEB 18", "MAR 18", etc. I have a userform that initiates prior to the macro being ran. I am able to enter a new year (ie: 2019), however the code continues to display "18" for my form year. It is like the entry into the userform is not working. The script I am using utilizes a case statement to select the desired files with a previous year and replace it with the current year.

Sub NewFiscalYear()

Dim ws As Worksheet
Dim PrevYear As Integer
Dim FormYear As Integer

FormYear = Format(Date, "yy")
PrevYear = FormYear - 1

'Disabling Display Alerts
Application.DisplayAlerts = False
Application.ScreenUpdating = False

    'Changing FY Date on Forms
     If FormMonth = "AUG" Then

        For Each ws In Worksheets
            Select Case ws.Name
                Case "JAN " & PrevYear, "FEB " & PrevYear, "MAR " & PrevYear, "APR " & PrevYear, _
                "MAY " & PrevYear, "JUN " & PrevYear, "JUL " & PrevYear, "AUG " & PrevYear, _
                "SEP " & PrevYear, "OCT " & PrevYear, "NOV " & PrevYear, "DEC " & PrevYear
                    ws.Name = "ws.name " & FormYear
            End Select
        Next
    End If

I am working on a large report that gathers information from multiple other workbooks. My userform includes a date to enter (FormYear) and a month to choose from via a combo box (FormMonth). Once these are selected, the macro is ran. I have the report working great, however have been running into hiccups with the new FY.

*****My goal is to have users run the report on the first month of the new fiscal year, hopefully without them noticing anything has changed (with the exception of it looking like a brand new worksheet). The tabs indicate the current month that was selected along with the fiscal year that was selected in the userform prior to running the macro. Once the tabs are identified with the new year, I am going to go through and reset all of the forms with blank data. I hope this made things a bit more clear!*****

Any tips or tricks would be mostly appreciated!

Upvotes: 0

Views: 549

Answers (2)

FreeMan
FreeMan

Reputation: 5687

FormYear = Format(Date, "yy") will return 18 until the clock rolls around on Jan 1, 2019.

If you need to get 19, now, you need either:

FormYear = CInt(Format(Date, "yy")) + 1

or

FormYear = CInt(Format(DateAdd("yyyy", 1, Date),"yy"))

After further clarification, this should do what you're after:

Sub NewFiscalYear()

  Dim ws As Worksheet
  Dim PrevYear As Integer
  Dim FormYear As String

  FormYear = CInt(Format(Date, "yy"))
  PrevYear = CStr(FormYear - 1)

Note the explicit conversion from strings to integers and back. VBA will do this for you, but this way you (and everyone else) knows it's intentional 

'remove these lines until everything works fine
  'Disabling Display Alerts
'  Application.DisplayAlerts = False
'  Application.ScreenUpdating = False

Declare a constant here so you know why this "magic value" is there. You know that's the start of the fiscal year, but 5 years from now, when that changes, someone else may not know why this doesn't work anymore and not realize that the FY start changed.

  Const FISCAL_YEAR_START_MONTH as String = "AUG"
  'Changing FY Date on Forms
  If FormMonth = FISCAL_YEAR_START_MONTH Then

    For Each ws In Worksheets
      Select Case ws.Name
        Case "JAN " & PrevYear, "FEB " & PrevYear, "MAR " & PrevYear, "APR " & PrevYear, _
             "MAY " & PrevYear, "JUN " & PrevYear, "JUL " & PrevYear, "AUG " & PrevYear, _
             "SEP " & PrevYear, "OCT " & PrevYear, "NOV " & PrevYear, "DEC " & PrevYear

Once you've found a worksheet whose naming pattern matches what you're looking for, extract the first 3 characters (the month name) and append the new year to the end of it.

          ws.Name = Left(ws.Name, 3) & " " & CStr(FormYear)
      End Select
    Next
  End If

'disabled them earlier, make sure we reenable them now
'doesn't hurt to enable them if they weren't disabled earlier
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

Upvotes: 2

CLR
CLR

Reputation: 12279

Your renaming line is all wrong:

ws.Name = "ws.name " & FormYear

This would attempt to change "JAN 18" to "ws.name 19" because you've wrapped the object in quotes.

Even if you got the quotes right:

    ws.Name = ws.name & " " & FormYear

It would change "JAN 18" to "JAN 18 19"

Try:

ws.Name = Replace(ws.Name, PrevYear, FormYear)

EDIT

Also, I think your PrevYear/FormYear might be incorrect.

I'd go with:

PrevYear = Format(Date, "yy") ' so this is the current year value
FormYear = PrevYear + 1       ' this becomes next year value

Upvotes: 3

Related Questions