Sorry all, I posted this earlier in the wrong forum. I'll try again.

Hi All ....

This is my first post on this board.

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

This is the code I use (the first part sends the new worksheet to th
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") & " Alread
Exists ... Rename This WorkSheet."

End Sub

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

Any ideas out there??

Regards ..... g-fer

--
G-fe
-----------------------------------------------------------------------
G-fer's Profile: http://www.excelforum.com/member.php...fo&userid=3775
View this thread: http://www.excelforum.com/showthread.php?threadid=57338

Please don't multipost. If you want to post to several newsgroups, do it all at
once (one message, multiple "to:'s").

You have a response at your other post.

G-fer wrote:

Sorry all, I posted this earlier in the wrong forum. I'll try again.

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.

--
G-fer
------------------------------------------------------------------------
G-fer's Profile: http://www.excelforum.com/member.php...o&userid=37756
View this thread: http://www.excelforum.com/showthread...hreadid=573384

--

Dave Peterson