[RESOLVED] Renaming Worksheets

Printable View

Feb 24th, 2006, 11:00 PM
Danno

[RESOLVED] Renaming Worksheets

I'm building a schedule in Excel with each worksheet tab indicating a particular day. My code keeps a date in one cell on each worksheet. I've been trying to write code that will rename the worksheet to exactly what is in this date cell, and nothing I've tried works. I keep getting error messages. Not even code from the macro recorder works. Does anyone know some code that will rename a worksheet to whatever is in a particular cell?

Thanks,

Danno
Feb 26th, 2006, 04:32 AM
Lemon Lime

Re: Renaming Worksheets
Hey Danno, Welcome to VBForums!!!

if your object is ("excel.worksheet") just
VB Code:
```
ExcelSheet.worksheets(1).Name = "whatever"
```
have a nice day!!
Feb 26th, 2006, 09:24 AM
Danno

Re: Renaming Worksheets

Yes, I understand the classic naming of a worksheet. My problem is: I need to name the worksheet to exactly what is in a particular cell in each worksheet. In my project, cell A23 holds a date. I need each worksheet to be named the date that is in cell A23. I can get it to work with the last worksheet in the workbook with the (sheets.count) code, but I can't get it to work with any other worksheet. Frustrating...

Danno

PS: Thanks for the welcome :wave:

Re: Renaming Worksheets

This might give you some ideas

VB Code:

Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    
    Set oExcel = New Excel.Application
    Set oBook = oExcel.Workbooks.Open("C:\projects\testing.xls")
    oExcel.Visible = True
    
    For Each oSheet In oBook.Worksheets
        'assumes cell A23 is a valid date 
        'The Sheet Name cannot contain the characters \ / ? * [ ]
        oSheet.Name = Format$(oSheet.Cells(23, 1), "dd-MMM-yyyy") 
    Next
    
    oBook.Close True
    Set oBook = Nothing
    
    oExcel.Quit
    Set oExcel = Nothing

Feb 26th, 2006, 02:14 PM
Danno

Re: Renaming Worksheets

It doesn't like this line of your code:

oSheet.Name = Format$(oSheet.Cells(23, 1), "dd-MMM-yyyy")

It produces the same error messages I've been getting about bad spelling and/or illegal characters in the worksheet name. The weird thing is that it will work if all you want to do is rename the last worksheet because you can use the (sheet.count) code. Excel just seems to reject the automatic renaming of any other sheet except the last one. I know I'm doing something wrong, just don't know what.

Danno
Feb 26th, 2006, 02:19 PM
RobDog888

Re: Renaming Worksheets

What is the data in cell - oSheet.Cells(23, 1)? If its a blank string or non-date type data then you will get the error.
Feb 27th, 2006, 03:34 PM
lexII

Re: Renaming Worksheets
I used this to rename worksheets in an old project of mine.
H3 is used to store the date in a text format (from A23) before being deleted
VB Code:
```
' Rename New Sheet to dd-mmm
Range("H3").Formula = "=Text(A23, ""dd"" & ""-"" & ""mmm"")"
ActiveSheet.Name = Range("H3")
Range("H3").Formula = ""
```
adding the year should be easy enough. ..mm"" & ""-"" & ""yyyy"")"
Feb 27th, 2006, 07:48 PM
Danno

Re: Renaming Worksheets

Thank you LexII... your code worked!!

I've added to your code to customize mine to exactly what I need.

Thanks once again...Problem Resolved!!

Danno
Feb 28th, 2006, 12:29 AM
RobDog888

Re: Renaming Worksheets

Now that your thread is solved dont forget to 'Resolve' your original post so other members can see from the forum view level that its Resolved.
Feb 28th, 2006, 09:10 AM
Webtest

Re: Renaming Worksheets

Danno ... what version of Excel are you running? Everything I have tried (without having to use an intermediary cell) in 2003 works just fine.
Feb 28th, 2006, 10:08 AM
billhuard

Re: Renaming Worksheets

WebTest, I think the problem is coming from the way excel stores the value in a cell.
All cells that are DATE stores the value like this 28/02/06. Even if the cell display 28-02-06 or 28-feb-06 or whatever, the data is stored like 28/02/06 in the cell.

So if you ask excel to rename a sheet with the value of a cell that is DATE, you've got an error because sheet name cannot have "/" in it.

This is why Danno got error and you don't.
Feb 28th, 2006, 10:23 AM
Webtest

Re: Renaming Worksheets

Quote:

Originally Posted by billhuard

All cells that are DATE stores the value like this 28/02/06. Even if the cell display 28-02-06 or 28-feb-06 or whatever, the data is stored like 28/02/06 in the cell.

So if you ask excel to rename a sheet with the value of a cell that is DATE, you've got an error because sheet name cannot have "/" in it.

Thanks, but Danno said the following:

Quote:

Originally Posted by Danno

It doesn't like this line of your code:

oSheet.Name = Format$(oSheet.Cells(23, 1), "dd-MMM-yyyy")

... which does NOT have any "/" characters in it ... this function works for me just fine.
Feb 28th, 2006, 12:45 PM
billhuard

Re: Renaming Worksheets

You're right
the function worked fine for me too.

better roll my tongue 7 times before writing next time :)
Feb 28th, 2006, 05:52 PM
Danno

Re: [RESOLVED] Renaming Worksheets

To Webtest and billhuard,

I am still stuck with Excel 2000. billhuard is absolutely correct. What I didn't know is that even if you store the date in a cell correctly (no illegal characters), Excel still stores the date with a / as the divider which is an illegal character for renaming worksheets. I'm glad to hear that this is not a problem in Excel 2003, maybe my company will spring for it now.

I'm pretty new to VBA and it is just the coolest thing to see what you can do to Excel with some VBA code. The more I learn the more intoxicated with power I become!! :bigyello:

Thanks again one and all.

Danno
Mar 1st, 2006, 08:24 AM
Webtest

Re: [RESOLVED] Renaming Worksheets

Danno ...

I don't think it is a problem with Excel 2000. I tried a few things in Excel '97 AND Excel 2003 ... here are the results ...

Code:

'Cells(1,1) contains value 3/4/2006 display formatted as 3-Mar-06 'BOTH OF THE FOLLOWING RUN FINE IN XL-'97 AND XL-2003 ActiveSheet.Name = Format$(ActiveSheet.Cells(1, 1).Value, "dd-MMM-yyyy") ActiveSheet.Name = Cells(1, 1).Text '<Here is one place where Value and Text are DIFFERENT!!! 'FAILS due to illegal characters in Sheet Name ActiveSheet.Name = Cells(1, 1).Value

Yes, VBA is very powerful. The book "Professional Excel Development" by Bullen, Bovey, & Green (Addison Wesley) is dedicated to building amazing Graphic Form based applications on the same order as Visual Basic applications. This book proclaims that Excel is "not just spreadsheets", but a rich development platform. It really digs into the buried power of VBA running under Excel.

Good Luck and Good Programming!
Mar 1st, 2006, 01:23 PM
RobDog888

Re: [RESOLVED] Renaming Worksheets

.Text and .Value can hold different values so make sure they are the same.
Mar 1st, 2006, 08:27 PM
Danno

Re: [RESOLVED] Renaming Worksheets

Webtest,

Thanks for the code and for the book recommendation. I'll get it ordered today.

Danno :thumb:
Mar 1st, 2006, 08:29 PM
Danno

Re: [RESOLVED] Renaming Worksheets

RobDog888,

You're right about .value and .text. I'll be careful :thumb:

Danno
Mar 2nd, 2006, 07:52 AM
Webtest

Re: [RESOLVED] Renaming Worksheets

If you are interested in the "nuts and bolts" technical details of Excel VBA, another reference that I like is:

"Definitive Guide to Excel VBA"
Michael Kofler
a! Press

Neither of the books that I've mentioned will answer all of your questions, but between them and the Forum, you have access to an amazing depth of knowledge!!!
Mar 2nd, 2006, 09:18 PM
Danno

Re: [RESOLVED] Renaming Worksheets

Got it Webtest, I'll get that one too.

Danno