OssieMac,

Hey, your correct! (of course)
Sorry for the delayed response as I have not been back to this post since I
got the answer ( or thought I had). It worked fine through the end of
December then when I came back on Jan 4th I found the problem. I had already
made the change that you are giving me here.

Thanks again,

RR

"OssieMac" wrote:

Hi again Ron,

Thanks for the feedback. However, using your method for formatting the
worksheet name returns only a single digit for days 1 to 9 resulting in the
left 10 characters returning a space on the end effectively creating 2 spaces
between the date and the number in brackets. If you use the format function
you can control this to return 2 digits for the day.

Also, I have modified the code again to create a worksheet variable in lieu
of a string for the added sheet name. That way, you do not have to select the
sheet to name it; just refer to it by the variable.

Sub NameWorkSheet2()
Dim CurSht As Worksheet
Dim Rdate As String
Dim x As Integer

Sheets.Add
Set CurSht = ActiveSheet

'Format function permits any valid date format
'and guarantees the number of digits if dd is
'used for the day.
Rdate = Format(Date, "mm dd yyyy")

Do
On Error Resume Next
Worksheets(Rdate).Select
If Err.Number 0 Then
On Error GoTo 0
Exit Do 'Worksheet does not exist
Else
On Error GoTo 0
x = x + 1
Rdate = (Left(Rdate, 10) & " (" & x & ")")
End If
Loop

'with the worksheet assigned to a variable there is
'no need for it to be the active sheet to reference it.
CurSht.Name = Rdate

End Sub

--
Regards,

OssieMac


"Ron5440" wrote:

Thanks OssieMac...
I modified as such to make work...I used CurSht to set my current sheet name
after checking to see if the sheet existed to get back to the correct sheet.

Sheets.Add
Rdate = Month(Date) & " " & Day(Date) & " " & Year(Date)
CurSht = ActiveSheet.Name

Do
On Error Resume Next
Worksheets(Rdate).Select
If Err.Number 0 Then
On Error GoTo 0
Worksheets(CurSht).Select
Exit Do 'Worksheet does not exist
Else
On Error GoTo 0
x = x + 1
Rdate = (Left(Rdate, 10) & " (" & x & ")")
End If
Loop
ActiveSheet.Name = Rdate



"OssieMac" wrote:

Hi ron,

Sub NameWorkSheet()

Dim Rdate As String
Dim x As Integer
Dim strDateFormat As String

'Edit "dd-mm-yy" to required
'date format for worksheet
strDateFormat = "dd-mm-yy"
Rdate = Format(Date, strDateFormat)

Do
On Error Resume Next
Worksheets(Rdate).Select
If Err.Number 0 Then
On Error GoTo 0
Exit Do 'Worksheet does not exist
Else
On Error GoTo 0
x = x + 1
Rdate = Format(Date, strDateFormat) _
& " (" & x & ")"
End If
Loop

'worksheet must be added after test to ensure
'that it is the active sheet for naming
Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = Rdate

End Sub

--
Regards,

OssieMac