Renaming the Worksheet through VBA

[abxy]

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

 

[Brad]

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

 

[Tom Ogilvy]

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

or "mm_dd_yyyy"

But you should get the idea.

 

[abxy]

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

 

[Bob Phillips]

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)