After all of these episodes about using the macro recorder, Jonathan tried to record a macro to rename worksheets. The code fails sometimes and works other times. We will take a look at that code and understand how to generalize it to have it always work in Episode 814.
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:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
We have a great question today sent in by Jonathan, and, actually, yesterday's podcast started out with Jonathan's problem, but I wanted to share that macro.
Yesterday, we recorded a tiny little macro that said, look, we start at workbook, it has 3 sheets, we want to rename those sheets to REVENUE, COSTS, and EXPENSES, and we recorded this macro over here on the right-hand side.
Now, Jonathan did the exact same thing, and then I said, okay, well, let's test the macro and see how it works.
So, we click our MACROS button, we say that we want to run MACRO9, and it errors out, and this is so strange me.
What's going on?
It's a tiny little macro.
It hardly does anything at all, and you wonder why it doesn't work, and here's what's even more frustrating.
I'm going to do FILE, NEW, and start with a new blank workbook, and, on this new blank workbook, I try and run the macro again.
So, MACROS, run MACRO9, and it works perfectly.
It changed our names to REVENUE, COSTS, and EXPENSES.
Well, what's going on?
Let's take a look at the macro code, and what it did here is it kind of hard-coded.
It said, hey, look, we're going to go grab that sheet called SHEET1 and we're going to rename it to be REVENUE, and then we're going to grab SHEET2 and rename it to be COSTS, and then we're going to grab SHEET3 and rename it to be EXPENSES.
Well, when we try and test this macro on a workbook which it has already been run, there is no SHEET1 because now SHEET1 is called REVENUE, and there is no SHEET2 because SHEET2 is called COSTS.
So, what we need to do to correct this is, instead of explicitly saying, hey, go to the sheet called SHEET1 or the sheet called SHEET2, we're just going to use an index number.
So, SHEET 1 .NAME and then SHEET 2 .NAME and then SHEET3 .NAME.
Now, I have to tell you, this is fairly dangerous because, you know, if someone runs this on another workbook, there's going to be havoc.
You're just going to start renaming sheets randomly, but you'll see here that we can basically run this macro now, this shorter macro, on a worksheet, and even if it's already been run before, even if we already have REVENUE, COSTS, and EXPENSES, it will run successfully, alright?
So, there’s some times that the macro recorder just simply cannot do what you need it to do, and then we need to switch over to the VBA window and write a little bit of code.
Now, if you're interested in learning VBA, we have a couple of resources.
I have the book VBA and Macros for Microsoft Excel.
It assumes you know Excel very well, and if you're watching this podcast, I'm going to say that you know Excel very well but that you've never really been past the macro recorder.
It will show you how to tweak the macro recorder, and then how to actually understand the recorded code, and be able to take the recorded code and tweak it a little bit to make it work all the time every time.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
We have a great question today sent in by Jonathan, and, actually, yesterday's podcast started out with Jonathan's problem, but I wanted to share that macro.
Yesterday, we recorded a tiny little macro that said, look, we start at workbook, it has 3 sheets, we want to rename those sheets to REVENUE, COSTS, and EXPENSES, and we recorded this macro over here on the right-hand side.
Now, Jonathan did the exact same thing, and then I said, okay, well, let's test the macro and see how it works.
So, we click our MACROS button, we say that we want to run MACRO9, and it errors out, and this is so strange me.
What's going on?
It's a tiny little macro.
It hardly does anything at all, and you wonder why it doesn't work, and here's what's even more frustrating.
I'm going to do FILE, NEW, and start with a new blank workbook, and, on this new blank workbook, I try and run the macro again.
So, MACROS, run MACRO9, and it works perfectly.
It changed our names to REVENUE, COSTS, and EXPENSES.
Well, what's going on?
Let's take a look at the macro code, and what it did here is it kind of hard-coded.
It said, hey, look, we're going to go grab that sheet called SHEET1 and we're going to rename it to be REVENUE, and then we're going to grab SHEET2 and rename it to be COSTS, and then we're going to grab SHEET3 and rename it to be EXPENSES.
Well, when we try and test this macro on a workbook which it has already been run, there is no SHEET1 because now SHEET1 is called REVENUE, and there is no SHEET2 because SHEET2 is called COSTS.
So, what we need to do to correct this is, instead of explicitly saying, hey, go to the sheet called SHEET1 or the sheet called SHEET2, we're just going to use an index number.
So, SHEET 1 .NAME and then SHEET 2 .NAME and then SHEET3 .NAME.
Now, I have to tell you, this is fairly dangerous because, you know, if someone runs this on another workbook, there's going to be havoc.
You're just going to start renaming sheets randomly, but you'll see here that we can basically run this macro now, this shorter macro, on a worksheet, and even if it's already been run before, even if we already have REVENUE, COSTS, and EXPENSES, it will run successfully, alright?
So, there’s some times that the macro recorder just simply cannot do what you need it to do, and then we need to switch over to the VBA window and write a little bit of code.
Now, if you're interested in learning VBA, we have a couple of resources.
I have the book VBA and Macros for Microsoft Excel.
It assumes you know Excel very well, and if you're watching this podcast, I'm going to say that you know Excel very well but that you've never really been past the macro recorder.
It will show you how to tweak the macro recorder, and then how to actually understand the recorded code, and be able to take the recorded code and tweak it a little bit to make it work all the time every time.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
