Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet renaming | Excel Discussion (Misc queries) | |||
Worksheet Renaming | Excel Discussion (Misc queries) | |||
Worksheet Renaming | Excel Worksheet Functions | |||
Renaming Worksheet without using mouse?? | Excel Discussion (Misc queries) | |||
need help renaming a worksheet | Excel Discussion (Misc queries) |