zoom38
01-30-2015, 10:33 AM
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
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