PDA

View Full Version : [SOLVED] Dates Used as Sheet Names



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

Kenneth Hobs
01-30-2015, 11:56 AM
Use CDate() to convert a date string to a date for your comparisons.

zoom38
02-03-2015, 07:49 AM
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