Your recorded macros might not work if someone renames a worksheet. Rather than use the worksheet name, use the code name for the worksheet. This name can never be changed and is more reliable. Episode 1096 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we gonna analyze this.
Well, let's fire up a pivot table and see you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today a great question just sent in by Craig.
I had a throw on the the camera here and do a quick podcast about it, Craig recorded some VBA and Craig is worried that the Macro, isn't going to work if someone renames one of the sheets.
So, let's just do this here real fast you see we have sheets MyData, Archive, Report and Sheet 1.
I'm going to turn on the Macro Recorder. I'll just call this QuickTest and click OK and so, you know Craig record a Macro, where he selected MyData. You know and just I don't know type something that it's not important what he was doing.
The issue here is what happens if the name changes.
So now, we can do a little test of the Macro, come to another sheet and we'll run the Macro with Alt+F8, QuickTest run works great.
Okay, but what would happen if someone comes along, and just renames it to be data.
Well now, we're going to have some trouble because when we run that Macro, it's going to give us 1004 error, it gonna say hey, that subscript.
Oh no, it's an a 9 error, Run-time 9 error subscript out of range because you know it's saying hey, go activate the my data worksheet, and it doesn't exist.
So, let's go look at that code.
Now, here's the solution to this once you look over here in the project explorer, the project explorer shows a list of all the worksheets and here's the names that we know Data, Archive, Report, Sheet 1.
That's the regular name and that's the name that you would use in quotes inside of the sheets or worksheets collection, but there's another name Sheet 1, Sheet 2, Sheet 3, Sheet 4, these are the code names and the beautiful thing is Microsoft assigns these code names, and they will never change no matter what happens out in the excel user interface.
So, someone renames the worksheet this is always going to be called sheet 1 and so simply what we do we don't have to put it in quotes or parentheses around ahead, just put the the code pain name Sheet 1.select, that's all you would need to do.
So, let's will stop this will come back out here. We'll select something, Alt+F8 run the QuickTest, Macro and sure enough it selects data it doesn't matter we could call it anything we wanted and it is still going to come back and always find the sheet that we wanted to find.
Alt+F8, QuickTest, click Run.
So, there you have it.
Use the worksheet code name instead of the real name when we are working in VBA.
Great question from Craig common problem and a great little solution here using these other worksheet names.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we gonna analyze this.
Well, let's fire up a pivot table and see you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today a great question just sent in by Craig.
I had a throw on the the camera here and do a quick podcast about it, Craig recorded some VBA and Craig is worried that the Macro, isn't going to work if someone renames one of the sheets.
So, let's just do this here real fast you see we have sheets MyData, Archive, Report and Sheet 1.
I'm going to turn on the Macro Recorder. I'll just call this QuickTest and click OK and so, you know Craig record a Macro, where he selected MyData. You know and just I don't know type something that it's not important what he was doing.
The issue here is what happens if the name changes.
So now, we can do a little test of the Macro, come to another sheet and we'll run the Macro with Alt+F8, QuickTest run works great.
Okay, but what would happen if someone comes along, and just renames it to be data.
Well now, we're going to have some trouble because when we run that Macro, it's going to give us 1004 error, it gonna say hey, that subscript.
Oh no, it's an a 9 error, Run-time 9 error subscript out of range because you know it's saying hey, go activate the my data worksheet, and it doesn't exist.
So, let's go look at that code.
Now, here's the solution to this once you look over here in the project explorer, the project explorer shows a list of all the worksheets and here's the names that we know Data, Archive, Report, Sheet 1.
That's the regular name and that's the name that you would use in quotes inside of the sheets or worksheets collection, but there's another name Sheet 1, Sheet 2, Sheet 3, Sheet 4, these are the code names and the beautiful thing is Microsoft assigns these code names, and they will never change no matter what happens out in the excel user interface.
So, someone renames the worksheet this is always going to be called sheet 1 and so simply what we do we don't have to put it in quotes or parentheses around ahead, just put the the code pain name Sheet 1.select, that's all you would need to do.
So, let's will stop this will come back out here. We'll select something, Alt+F8 run the QuickTest, Macro and sure enough it selects data it doesn't matter we could call it anything we wanted and it is still going to come back and always find the sheet that we wanted to find.
Alt+F8, QuickTest, click Run.
So, there you have it.
Use the worksheet code name instead of the real name when we are working in VBA.
Great question from Craig common problem and a great little solution here using these other worksheet names.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
