My code is having trouble assigning a new name to a worksheet. I thought
this would work ws2.Name = ws1.Range("L4"). Cell "L4" on ws1 contains the
function TODAY(). I thought this would go ahead and assign todays date to
the name of ws2. The code breaks and the run time error does mention I
cannot use any / \ * etc. in the name. I tried to format the date to
something like 3-10-06 but to no avail. What the heck is wrong with this
simple request?

Give this a whirl...

Sub test()
Dim wks As Worksheet
Dim strName As String

Set wks = Sheet2
strName = Format(Date, "mm-dd-yyyy")
If SheetExists(strName) Then
MsgBox "Sheet " & strName & " already exists."
Else
wks.Name = strName
End If

End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
--
HTH...

Jim Thomlinson


"Coal Miner" wrote:

My code is having trouble assigning a new name to a worksheet. I thought
this would work ws2.Name = ws1.Range("L4"). Cell "L4" on ws1 contains the
function TODAY(). I thought this would go ahead and assign todays date to
the name of ws2. The code breaks and the run time error does mention I
cannot use any / \ * etc. in the name. I tried to format the date to
something like 3-10-06 but to no avail. What the heck is wrong with this
simple request?

Maybe...

ws2.Name = format(ws1.Range("L4").value, "YYYY_MM_DD")

or just

ws2.Name = format(date, "YYYY_MM_DD")



Coal Miner wrote:

My code is having trouble assigning a new name to a worksheet. I thought
this would work ws2.Name = ws1.Range("L4"). Cell "L4" on ws1 contains the
function TODAY(). I thought this would go ahead and assign todays date to
the name of ws2. The code breaks and the run time error does mention I
cannot use any / \ * etc. in the name. I tried to format the date to
something like 3-10-06 but to no avail. What the heck is wrong with this
simple request?

--

Dave Peterson

Dave and Jim,

Thanks. All is well now!

"Dave Peterson" wrote:

Maybe...

ws2.Name = format(ws1.Range("L4").value, "YYYY_MM_DD")

or just

ws2.Name = format(date, "YYYY_MM_DD")



Coal Miner wrote:

My code is having trouble assigning a new name to a worksheet. I thought
this would work ws2.Name = ws1.Range("L4"). Cell "L4" on ws1 contains the
function TODAY(). I thought this would go ahead and assign todays date to
the name of ws2. The code breaks and the run time error does mention I
cannot use any / \ * etc. in the name. I tried to format the date to
something like 3-10-06 but to no avail. What the heck is wrong with this
simple request?

--

Dave Peterson