Autodate worksheet tab

[G-fer]

Hi All ....

This is my first post on this board.

I currently use a small piece of code to automatically name a new
worksheet todays date. If I open a second worksheet on the same day, I
trap the error caused by duplicating the worksheet name and create a
message box telling me to rename the new worksheet.

This is the code I use (the first part sends the new worksheet to the
back of the workbook).


Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error GoTo lookout
Sh.Move After:=Sheets(Sheets.Count)
Sh.Name = Format(Date, "dd-mmm-yyyy")

Exit Sub

lookout:

MsgBox "Worksheet Called " & Format(Date, "dd-mmm-yyyy") & " Already
Exists ... Rename This WorkSheet."

End Sub

What I would like to do is, add an underscore plus a number after the
date if the worksheet of that date already exists. For example, the
first worksheet generated today is called 19-Aug-2006 and I would like
to call the next one generated today 19-Aug-2006_1, and so on, rather
than receive the message box telling me that a sheet of that name
already exists.

Any ideas out there??

Regards ..... g-fer.

 

[Dave Peterson]

I'd use a format of dd_mmm_yyyy_001, since it could make sorting the worksheets
easier.

Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim iCtr As Long
Dim myStr As String

iCtr = 0
Do
Sh.Move After:=Sheets(Sheets.Count)
If iCtr = 0 Then
myStr = ""
Else
myStr = Format(iCtr, "_000")
End If
On Error Resume Next
Sh.Name = Format(Date, "dd-mmm-yyyy") & myStr
If Err.Number = 0 Then
Exit Do
End If
iCtr = iCtr + 1
Loop

End Sub

And if you ever want to sort the worksheets...

Chip Pearson's:
http://www.cpearson.com/excel/sortws.htm

David McRitchie's:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets

 

[G-fer]

Thanks Dave ...

I'll give it a try. Sorry about the duel post ... won't happen again.

G-fer.