Renaming the Worksheet through VBA

  • Thread starter Thread starter abxy
  • Start date Start date
A

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
 
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
 
activesheet.Name = Format(Range("F8"),"mmddyy")

or "mm_dd_yyyy"

But you should get the idea.
 
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
 
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)
 
Back
Top