Episodes #1488 and #1489 of the "Learn Excel from MrExcel Podcasts" generated a lot of email from viewers. Today, in Episode #1497, Bill revisits those episodes [that were dealing with making copies of worksheets]. Looking at several methods emailed in over the last week, Bill demonstates even more Copy Worksheet methods - including a VBA Macro routine!
...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!
"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!
"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 1497: Sheets Revisited.
We’re going to go back to episodes 1488 and 1489. Those have generated a lot of email.
I was trying to make copies of sheet 1, and I did that by holding down CONTROL and dragging to the right, alright, and a few people…or CarpyJedi wrote in and said, well, do that a few times.
Select the first sheet, SHIFT-click the last sheet, make sure you go back to the first sheet, hold down the CONTROL key, and drag the whole thing to the right.
Now you're knocking out 5 sheets at a time instead of 1 at a time.
Good idea.
Next.
OS Mr Ducks in Denver said, hey, here's a cool trick, because in 1489, I then had to rename those sheets.
Start with DAY as the name, and as you make copies, the second one will be called DAY2, DAY3, DAY4, DAY5, and then when you get done, go back here and rename this one to be DAY(1), and you’re good to go that way, alright?
Clever.
Clever.
I like that trick.
And then, ASD just…it was a one-line reply.
Macro?
Well, of course macro.
That's the way to go.
In a past podcast, we talked about the fill handle sheets, fill handle sheets, which is like, take this day 1 and pretend that you put day 1 here, grab the fill handle and drag, and, you know, it will fill.
Why can't the sheet copy automatically do that?
Well, I wrote a macro.
CONTROL+SHIFT+F. How many sheets do I want?
I want 30, click OK, and it goes through and uses that fill handle logic to rename the sheets.
Here, let’s try this one.
Let's call this one Monday and CONTROL+SHIFT+F.
How many new ones do I want?
I want 6, click OK, and bam.
Tuesday, Wednesday, Thursday, Friday.
So, that code is here.
Let's just take a look at how this is working.
You want the code, shoot me a note, bill@mrexcel.com.
It actually figures out how many sheets do you want, it figures out the name in that sheet, and creates a brand new workbook, hides the screen so you can't see it, creates a brand new workbook, puts that sheet name in, and then uses the fill handle to copy it down, and then, for each item in that new sheet, it is adding a worksheet to the end and using the cell from that new sheet, and then closes that temporary sheet.
So, kind of a little bit of code there.
I suppose if we had a specific list, it would be easier, but it's an interesting way to go through.
So, lots of good responses there to 1488 and 1489 making sheet copies.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1497: Sheets Revisited.
We’re going to go back to episodes 1488 and 1489. Those have generated a lot of email.
I was trying to make copies of sheet 1, and I did that by holding down CONTROL and dragging to the right, alright, and a few people…or CarpyJedi wrote in and said, well, do that a few times.
Select the first sheet, SHIFT-click the last sheet, make sure you go back to the first sheet, hold down the CONTROL key, and drag the whole thing to the right.
Now you're knocking out 5 sheets at a time instead of 1 at a time.
Good idea.
Next.
OS Mr Ducks in Denver said, hey, here's a cool trick, because in 1489, I then had to rename those sheets.
Start with DAY as the name, and as you make copies, the second one will be called DAY2, DAY3, DAY4, DAY5, and then when you get done, go back here and rename this one to be DAY(1), and you’re good to go that way, alright?
Clever.
Clever.
I like that trick.
And then, ASD just…it was a one-line reply.
Macro?
Well, of course macro.
That's the way to go.
In a past podcast, we talked about the fill handle sheets, fill handle sheets, which is like, take this day 1 and pretend that you put day 1 here, grab the fill handle and drag, and, you know, it will fill.
Why can't the sheet copy automatically do that?
Well, I wrote a macro.
CONTROL+SHIFT+F. How many sheets do I want?
I want 30, click OK, and it goes through and uses that fill handle logic to rename the sheets.
Here, let’s try this one.
Let's call this one Monday and CONTROL+SHIFT+F.
How many new ones do I want?
I want 6, click OK, and bam.
Tuesday, Wednesday, Thursday, Friday.
So, that code is here.
Let's just take a look at how this is working.
You want the code, shoot me a note, bill@mrexcel.com.
It actually figures out how many sheets do you want, it figures out the name in that sheet, and creates a brand new workbook, hides the screen so you can't see it, creates a brand new workbook, puts that sheet name in, and then uses the fill handle to copy it down, and then, for each item in that new sheet, it is adding a worksheet to the end and using the cell from that new sheet, and then closes that temporary sheet.
So, kind of a little bit of code there.
I suppose if we had a specific list, it would be easier, but it's an interesting way to go through.
So, lots of good responses there to 1488 and 1489 making sheet copies.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
