[RESOLVED] Renaming Worksheets

[Danno]

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

[Lemon Lime]

Hey Danno, Welcome to VBForums!!!

if your object is ("excel.worksheet") just

ExcelSheet.worksheets(1).Name = "whatever"

have a nice day!!

[Danno]

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

[brucevde]

This might give you some ideas

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

[Danno]

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

[RobDog888]

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.

[lexII]

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

' 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"")"

[Danno]

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

[RobDog888]

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.

[Webtest]

Danno ... what version of Excel are you running? Everything I have tried (without having to use an intermediary cell) in 2003 works just fine.

[billhuard]

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.

[Webtest]

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:

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.

[billhuard]

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

better roll my tongue 7 times before writing next time

[Danno]

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!!

Thanks again one and all.

Danno

[Webtest]

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 ...

'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!

[RobDog888]

.Text and .Value can hold different values so make sure they are the same.

[Danno]

Webtest,

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

Danno

[Danno]

RobDog888,

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


Danno

[Webtest]

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!!!

[Danno]

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

Danno