Yesterday, in Podcast Episode #1488, Bill showed us a cool way to make 30 copies of a worksheet. However, after creating the worksheets we were left with strange names like Sheet1 (2), Sheet1 (3) and so on. Horrible! Today, in Episode #1489, Bill shows us how - with one line of code in the VBA Immediate Window - to solve the remaining issue of renaming the new Worksheets.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
*Kindle is Coming to 'The MrExcel Excel Store'! Stay Tuned!*
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
*Kindle is Coming to 'The MrExcel Excel Store'! Stay Tuned!*
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1489: Rename 31 Worksheets.
Alright. Well, hey. If you were here yesterday, you saw that podcast where Damian asked how to create 30 copies of a worksheet and I showed a great way using CONTROL-drag, but, now, the worksheet names all have to be renamed, and, yes, we could go through and do this and still it’d, you know, be faster than what Damia was doing before, but there has to be a better way.
I know a lot of you don't like VBA but this one is just so flat out simple.
ALT+F11 to get to VBA, CONTROL+G, as in girl, to get to the IMMEDIATE window.
Then, we say FOR i = 1 TO 31 :, that's next line, and we’re going to say WORKSHEETS i .NAME is = to.
Now, what do we want it to be?
In this case, I have day space and then the number.
So, DAY space ” & and then i, and then another : to go to the next line, NEXT, alright?
So, right there, that tiny little bit of code, I'm going to press ENTER, alright?
That's all it took.
It's fixed now.
Now, if you happen to be in February or April or a day that doesn't have 31, then, right at the very end, that's going to provide an error.
So, I'm going to do 32 worksheets here, RUN-TIME ERROR 9, SUBSCRIPT OUT OF RANGE.
That's okay.
By the time you get to the error, it's fixed.
In February, you might have to click OK a couple of times because it's trying to rename sheets that aren't there.
Now, let's go back to Excel.
That's ALT+Q to return back to Excel.
Check that out.
All of the days all the way are automatically numbered just like that, alright?
So, what a bizarre little piece of code if you don't know VBA.
ALT+F11, CONTROL+G, type this, you know, and if you didn't want day, if you just wanted the day number like 1 through 31, instead putting day in there, just put that, alright, and that would…here.
Let’s just run it and I'll change this back to 31.
Press ENTER, that ran that bit of code.
Now, it's just numbered 1 through 31.
What a fabulous way to go through and renumber all those sheets and you'll see that they just go in perfect order straight across.
So, cool trick -- part 2 to solve Damia's problem.
Okay.
Well, hey.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1489: Rename 31 Worksheets.
Alright. Well, hey. If you were here yesterday, you saw that podcast where Damian asked how to create 30 copies of a worksheet and I showed a great way using CONTROL-drag, but, now, the worksheet names all have to be renamed, and, yes, we could go through and do this and still it’d, you know, be faster than what Damia was doing before, but there has to be a better way.
I know a lot of you don't like VBA but this one is just so flat out simple.
ALT+F11 to get to VBA, CONTROL+G, as in girl, to get to the IMMEDIATE window.
Then, we say FOR i = 1 TO 31 :, that's next line, and we’re going to say WORKSHEETS i .NAME is = to.
Now, what do we want it to be?
In this case, I have day space and then the number.
So, DAY space ” & and then i, and then another : to go to the next line, NEXT, alright?
So, right there, that tiny little bit of code, I'm going to press ENTER, alright?
That's all it took.
It's fixed now.
Now, if you happen to be in February or April or a day that doesn't have 31, then, right at the very end, that's going to provide an error.
So, I'm going to do 32 worksheets here, RUN-TIME ERROR 9, SUBSCRIPT OUT OF RANGE.
That's okay.
By the time you get to the error, it's fixed.
In February, you might have to click OK a couple of times because it's trying to rename sheets that aren't there.
Now, let's go back to Excel.
That's ALT+Q to return back to Excel.
Check that out.
All of the days all the way are automatically numbered just like that, alright?
So, what a bizarre little piece of code if you don't know VBA.
ALT+F11, CONTROL+G, type this, you know, and if you didn't want day, if you just wanted the day number like 1 through 31, instead putting day in there, just put that, alright, and that would…here.
Let’s just run it and I'll change this back to 31.
Press ENTER, that ran that bit of code.
Now, it's just numbered 1 through 31.
What a fabulous way to go through and renumber all those sheets and you'll see that they just go in perfect order straight across.
So, cool trick -- part 2 to solve Damia's problem.
Okay.
Well, hey.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
