Dates Used as Sheet Names

[zoom38]

Hello all, I have a sub that creates and adds a new sheet using a date that is chosen by the user as the sheet name. The sub works with one issue. If a later date is chosen than the dates (sheet names) that already exists, it inserts the new sheet at the beginning when I want it added in date order. Ex; if Jan 15, 2015 is chosen, I want it to be inserted between Jan 10, 2015 and Jan 25, 2015 however it adds it in the beginning, before the first sheet. A streamlined version of the file is attached.

 Application.ScreenUpdating = False              
  Application.EnableEvents = False
  
    frmCalendar.Show
    newdate = Format(newgamedate, "MMM DD, YYYY")
    
    SheetCount = Sheets.Count
    
    'compare sheet names
    For x = 3 To SheetCount
        
        If Sheets(x).Name > newdate Then
            Sheets(x).Copy before:=Sheets(x)
            ActiveSheet.Name = newdate
            GoTo z
        End If
            
        If Sheets(x).Name = newdate Then            'if the new game is on the same day
            Sheets(x).Name = newdate & "(1)"
            Sheets(x).Copy after:=Sheets(x)
            GoTo z
        End If
    Next x
    
z:  ActiveSheet.Unprotect
   
      
    Range("A1").Select
    ActiveSheet.Protect
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Sub

Subtracting the two dates will get a result so I don't understand why "If Sheets(x).Name > newdate Then" doesn't work. Can someone please advise.

thanks
Gary

[Kenneth Hobs]

Use CDate() to convert a date string to a date for your comparisons.

[zoom38]

Thanks Ken with CDate() and a little tweaking I was able to get it done. Although probably not the most effiecient way to get it done it works.

 For x = 3 To SheetCount
        sheetname = Left(Sheets(x).Name, 12)
        currentdate = CDate(sheetname)
        
        If x < SheetCount Then
            sheetname2 = Left(Sheets(x + 1).Name, 12)
        End If
        nextdate = CDate(sheetname2)
                
        If newgamedate > currentdate And newgamedate < nextdate Then
            Sheets(x).Copy after:=Sheets(x)
            ActiveSheet.Name = newdate
            GoTo z
        End If
        
        If newgamedate < currentdate Then
            Sheets(x).Copy before:=Sheets(x)
            ActiveSheet.Name = newdate
            GoTo z
        End If
        
        If newgamedate > Sheets(SheetCount).Name Then
            Sheets(x).Copy after:=Sheets(SheetCount)
            ActiveSheet.Name = newdate
            GoTo z
        End If
            
        If currentdate = newgamedate Then
            a = Len(Sheets(x).Name)
            countsheet = 0
            
            For Each WS In ThisWorkbook.Worksheets
                If StrComp(Left(WS.Name, 12), newdate, vbTextCompare) = 0 Then
                    countsheet = countsheet + 1
            End If
            Next WS
            
            If countsheet > 0 Then                       'if there is 1 or more existing sheets for the same date
                Sheets(x).Name = newdate & "(1)"         'this will add "(1)" to the 1st sheet with the same date
                Sheets(x).Copy after:=Sheets(newdate & "(" & countsheet & ")")
                GoTo z
              Else
                Sheets(x).Name = newdate & "(1)"         'if the new game is on the same day as the original,
                Sheets(x).Copy before:=Sheets(x + 1)     'this adds "(1)" to the original
            End If
        End If
    Next x
    End