Renaming the Worksheet through VBA
 

Ok, i'm trying to rename my worksheet to the contents of cell F8,
which really isn't that big of a deal. However, Cell F8 is a dat
formated as "mm/dd/yyyy" so when Excel tries to rename the worksheet
if gives me an error saying that it can't rename the worksheet becaus
the name contains invalid characters. I'ved tried reformatting the cel
itself to include only valid characters, but Excel still interprets i
the with "/" in it. So i know now that I have to tell Excel to forma
the date without the "/" in VBA, but how

--
Message posted from http://www.ExcelForum.com

Renaming the Worksheet through VBA
 

Try:

Sub RenameWorksheet()
Dim dat As Date
dat = Range("A1").Value '02/09/2004
MsgBox dat
Dim strName As String
strName = Month(dat) & "." & Day(dat) & "." & Year(dat)
ActiveSheet.Name = strName
End Sub

-----Original Message-----
Ok, i'm trying to rename my worksheet to the contents of
cell F8,
which really isn't that big of a deal. However, Cell F8
is a date
formated as "mm/dd/yyyy" so when Excel tries to rename
the worksheet,
if gives me an error saying that it can't rename the
worksheet because
the name contains invalid characters. I'ved tried
reformatting the cell
itself to include only valid characters, but Excel still
interprets it
the with "/" in it. So i know now that I have to tell
Excel to format
the date without the "/" in VBA, but how?


---
Message posted from http://www.ExcelForum.com/

.

Renaming the Worksheet through VBA
 

activesheet.Name = Format(Range("F8"),"mmddyy")

or "mm_dd_yyyy"

But you should get the idea.

--
Regards,
Tom Ogilvy


"Brad" wrote in message
...
Try:

Sub RenameWorksheet()
Dim dat As Date
dat = Range("A1").Value '02/09/2004
MsgBox dat
Dim strName As String
strName = Month(dat) & "." & Day(dat) & "." & Year(dat)
ActiveSheet.Name = strName
End Sub

-----Original Message-----
Ok, i'm trying to rename my worksheet to the contents of
cell F8,
which really isn't that big of a deal. However, Cell F8
is a date
formated as "mm/dd/yyyy" so when Excel tries to rename
the worksheet,
if gives me an error saying that it can't rename the
worksheet because
the name contains invalid characters. I'ved tried
reformatting the cell
itself to include only valid characters, but Excel still
interprets it
the with "/" in it. So i know now that I have to tell
Excel to format
the date without the "/" in VBA, but how?


---
Message posted from http://www.ExcelForum.com/

.

Renaming the Worksheet through VBA
 

Beautiful, that works, thanks a lot Tom! I knew the the jist of what t
do in VBA, but I just couldn't put it down right in the order so tha
it was doing what I wanted, but thanks to you, it works now, and an
another lesson in VBA learned.

Thanks man, you rock

--
Message posted from http://www.ExcelForum.com

Renaming the Worksheet through VBA
 

Another way is

Activesheet.Name = Range("F8").Text

but make sure there are no / in the date format, else it will error

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"abxy " wrote in message
...
Ok, i'm trying to rename my worksheet to the contents of cell F8,
which really isn't that big of a deal. However, Cell F8 is a date
formated as "mm/dd/yyyy" so when Excel tries to rename the worksheet,
if gives me an error saying that it can't rename the worksheet because
the name contains invalid characters. I'ved tried reformatting the cell
itself to include only valid characters, but Excel still interprets it
the with "/" in it. So i know now that I have to tell Excel to format
the date without the "/" in VBA, but how?


---
Message posted from http://www.ExcelForum.com/