Number of worksheet added that day.

Posted by Pete on September 17, 2001 11:10 PM

To anyone who can help.

I have a macro which contains a line which reads:
Sheets.Add.Name = Format(Now(), "dd-mm-yy")
It is great until I need to use the macro twice in one day. Is there any way using a macro to add the number of the worksheet created for that day. (i.e. name the first worksheet created on September 18 2001 as: 18-09-01 (1), and the second: 18-09-01 (2).

Thanx.

Posted by Liam on September 18, 2001 3:50 AM

Dim ws As Worksheet, x As Integer
x = 0
For Each ws In Worksheets
If Left(ws.Name, 8) = Format(Now(), "dd-mm-yy") Then x = x + 1
Next
Sheets.Add.Name = Format(Now(), "dd-mm-yy") & "(" & x + 1 & ")"

Posted by Allan on September 18, 2001 4:07 AM

Or .....

Or :-

Dim ws As Worksheet, x As Integer
x = 1
For Each ws In Worksheets
If Left(ws.Name, 8) = Format(Now(), "dd-mm-yy") Then x = x + 1
Next
Sheets.Add.Name = Format(Now(), "dd-mm-yy") & "(" & x & ")"
End Sub