Macro for renaming a worksheet who's name changes...

Ok, so here's the deal. I'm trying to make a macro, that in part renames an existing worksheet. Before this macro is run, the worksheet is the only worksheet that exists in the file. The only problem is the worksheet's name changes depending on which file I open. And everytime I record the macro, the VB code comes out as

"Sheets("SheetABC").Select
Sheets("ABC").Name = "newname"

The problem is in other files that this macro needs to be used on, the sheet is named BCA or XYZ or whatever. so the macro goes to select sheetABC but it's not there, because it has a different name, and I get an error message.

What the heck would the code be to make Excel always select the 1 worksheet that exists in each file, and rename it to 'newname'???

Originally Posted by Accipiter

Ok, so here's the deal. I'm trying to make a macro, that in part renames an existing worksheet. Before this macro is run, the worksheet is the only worksheet that exists in the file. The only problem is the worksheet's name changes depending on which file I open. And everytime I record the macro, the VB code comes out as

"Sheets("SheetABC").Select
Sheets("ABC").Name = "newname"

The problem is in other files that this macro needs to be used on, the sheet is named BCA or XYZ or whatever. so the macro goes to select sheetABC but it's not there, because it has a different name, and I get an error message.

What the heck would the code be to make Excel always select the 1 worksheet that exists in each file, and rename it to 'newname'???

Hello Accipiter,

Very good question. A worksheet is part of the worksheets collection. Every collection wether in VB or VBA can be retrieved in 2 ways. Either by it's string name as in "ABC" or it's place within the collection, which is a unique number.

See where I'm going with this? Since you only have the one sheet in each workbook, write your code so it uses the object collection index number instead of it's string name.

For example:

Worksheets("SheetABC") is the same as Worksheets.Item(1)

References what you want and you don't have to worry about what someone else has named it.

Hope this answers your question,
Leith Ross

So I could use the code Worksheets.Item(1).Name = "newname"...or Worksheets.(Sheet1).Name = "abc"

i'm kinda hazy on what the code should actually be...

Originally Posted by Accipiter

So I could use the code Worksheets.Item(1).Name = "newname"...or Worksheets.(Sheet1).Name = "abc"

i'm kinda hazy on what the code should actually be...

The beauty of this is you don't need to concern yourself with the sheet name. Just write your code as Worksheet.Item(1).

Here is what a Collection looks like:

Index String Name
1 "Abc"

Or think of it like an a String Array...

Worksheet(1) = "Abc"

Using the index we still reference the same object and don't have to worry about the name when accessing it.


Hope this is clearer,
Leith

it's definetly not working. I'm testing it out, this is the code I have for the macro:

Sub Macro1()

Worksheet(1) = "Abc"

End Sub


When I run it in one of the workbooks with only 1 worksheet that it needs to be used in i get this error:


"Compile error: Sub or function not defined"

Originally Posted by Accipiter

it's definetly not working. I'm testing it out, this is the code I have for the macro:

Sub Macro1()

Worksheet(1) = "Abc"

End Sub


When I run it in one of the workbooks with only 1 worksheet that it needs to be used in i get this error:


"Compile error: Sub or function not defined"

OK. The best thing to do is post the code and I'll edit the code so you can see what to do. Working with objects is tricky.

Thanks,
Leith

alright, thank you very much....Right now i have to get this macro to rename the workbook before i can go on to anything else. Maybe I'm missing something here, but this is all the code I have for it right now:



Sub Macro1()

Worksheet(1) = "Abc"

End Sub

Again, the person running it will open up a workbook, which will contain 1 worksheet, and then run the macro and have the worksheet be renamed to "Original" (that's the actual name it will bear, not 'newname'). The worksheet name will vary from workbook to workbook that is opened.

My apologies, I misunderstood. To rename the Workbook do this...

Sub Macro(1)

Worksheets.Item(1).Name = "Abc"

End Sub

This will rename your sheet "Abc"


Best,
Leith Ross

Accipiter,

It's logical the code doesn't work because it's not correct. The code should actually be:

Sub Macro1()
Worksheets(1) = "Abc"
End Sub

So Worksheets(1) instead of Worksheet(1), because that's the name the collection has. Normally, if you compile the project, Excel would point you to this problem.

Regards,
Bart

Well,

Two things to check. 1) See if Macro1 is a reserved or keyword and 2) This code should be in a Project Module. Not in the Sheet Code.

All I can think of.

Best,
Leith Ross

Your're right this is bizzare. To answer question about having done this before - Yes, many times. I have never encountered a problem like the one you having. Unless you're experiencing some system problems. When all else fails shutdown, run scandisk, disk cleaner, and defrag. Sorry, couldn't be more help. I know how frustrating programing can be. I've done it for a long time, 30 years now, 7 with just VBA.

Repost if you figure this out. I would love know what it is.

Sincerely,
Leith Ross

still no luck...just to make sure...

Sub Macro(1)

Worksheets.Item(1).Name = "Abc"

End Sub


that's the code for the macro to rename the one worksheet in a workbook to 'Abc' when the macro is run?


edit: uuuuuuuuuuugh apparently there's another macro on the computer called 'worksheets'.....i'm going rename it and see if this works....

success!! thanks leith and bart, turns out once i renamed the old existing macro that was already present to 'wsheet' everything worked out fine....man that was a frustrating 5 hours..haha....it was even worse because i went home last night and it worked fine on my computer at home, then i cmae into work again today and still couldn't get it to work here...thanks man

Worksheets.Item(1).Name = "Abc"