VBA Date issue

[Prish]

I have an excel workbook with worksheets named in the date format "mmm-yy".
my last worksheet on the right is named "Nov-16"

I have a macro to copy the last sheet and add a month to the date. Eg. the new sheet will be named Dec-16.
For some reason it named Dec-17, clicking the marco once more names the new sheet Jan-18 (this should have been Jan-17).

My code is as follows:

Sub NewMonthSheet()
 Dim lSht As Worksheet
 Dim nSht As Worksheet
 Dim shName As String
 Set lSht = Sheets(Sheets.Count)
 If IsDate(lSht.Name) Then
 shName = Format(DateAdd("m", 1, lSht.Name), "mmm-yy")
 On Error Resume Next 'Tests that sheet doesn't already exist
 Set nSht = Sheets(shName)
 On Error GoTo 0
 If nSht Is Nothing Then
 lSht.Copy After:=Sheets(Sheets.Count)
 Sheets(Sheets.Count).Name = shName
 Else
 MsgBox "Sheet """ & shName & """ already exists!" _
 , vbCritical
 End If
 Else
 MsgBox "Last sheet name does not" & Chr(10) _
 & "represent a month!", vbCritical
 End If
 End Sub

 

[GTO]

...I have a macro to copy the last sheet and add a month to the date. Eg. the new sheet will be named Dec-16.
For some reason it named Dec-17, clicking the marco once more names the new sheet Jan-18 (this should have been Jan-17).

I am missing the logic. You state that the code is to add a month to the date indicated by the last sheet. If the last sheet is December of 2017, then January 2018 would be a month later.

Mark

 

[steve the fish]

Its because Nov-16 will be seen as 16th November 2017 when read from the sheet name.

 

[Prish]

I am missing the logic. You state that the code is to add a month to the date indicated by the last sheet. If the last sheet is December of 2017, then January 2018 would be a month later.

Mark

The Last sheet is Nov-16, so the new sheet needs to be Dec-16, thereafter Jan-17 and so on.
I am only adding a month and not a year unless it is December. I understand the confusion, I meant to say I have renamed the Dec-17 sheet to Dec-16 and it still adds the new sheet as Jan-18.

 

[steve the fish]

This should work until march 2029....

    If Month(lSht.Name) > Month(Now) Then
        shName = Format(DateSerial(Year(lSht.Name) - 1, Month(lSht.Name) + 1, 1), "mmm-yy")
    Else
        shName = Format(DateAdd("m", 1, lSht.Name), "mmm-yy")
    End If

 

[Prish]

This should work until march 2029....

    If Month(lSht.Name) > Month(Now) Then
        shName = Format(DateSerial(Year(lSht.Name) - 1, Month(lSht.Name) + 1, 1), "mmm-yy")
    Else
        shName = Format(DateAdd("m", 1, lSht.Name), "mmm-yy")
    End If

Thanks, this works. Can I ask why it work until March 2029?

 

[GTO]

Hi there,

I believe I found the issue, leastwise in U.S. Excel, "Oct-16" is assumed to mean the 16th of October in the current year. Anyways, I didn't pursue that thought too much and Steve's may well be a better solution.

Option Explicit
  
Sub NewMonthSheet()
Dim lSht As Worksheet
Dim nSht As Worksheet
Dim shName As String
Dim lMonth As Long


  Set lSht = Sheets(Sheets.Count)
  
  On Error Resume Next
  lMonth = Application.Match(Left$(lSht.Name, 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)
  On Error GoTo 0
  
  If lMonth > 0 And Right$(lSht.Name, 2) Like "##" Then
    shName = Format(DateSerial(CLng(CStr(20) & Right$(lSht.Name, 2)), lMonth + 1, 1), "mmm-yy")
    On Error Resume Next 'Tests that sheet doesn't already exist
    Set nSht = Sheets(shName)
    On Error GoTo 0
    If nSht Is Nothing Then
      lSht.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = shName
    Else
      MsgBox "Sheet """ & shName & """ already exists!", vbCritical
    End If
  Else
    MsgBox "Last sheet name does not" & Chr(10) & "represent a month!", vbCritical
  End If
  
End Sub

Mark

 

[steve the fish]

Basically excel will see Nov-16 as 16th November with no year. So by default it will use the current year which is why when you run your macro in 2017 to add try to add the sheet Dec-16 you actually got Dec-2017 because excel added a month onto 16th November 2017. Once you try to add Mar-29 excel will then read Feb-29 as 1st of February 2029 because there are only 28 days in February.

 

[steve the fish]

Just thinking that this may work too:

 shName = Format(DateAdd("m", 1, DateValue("01-" & lSht.Name)), "mmm-yy")