J.B. needs to create a new worksheet for every customer listed on the summary worksheet. He asks if there is an Insert Worksheets command. While there isn't, you can create one with a few lines of VBA code. Episode 730 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.
Today, we have a question from JB.
JB says, "I have a worksheet with a whole series of customers and I need to create a worksheet for every single customer in the list." He says, "There's hundreds of them.
Is there a way that I could just select a range and you know, say create tabs or something like that." Well, unfortunately there isn't but we're Going to do this with a little bit of VBA, today.
But before I launch into the VBA, I want to do a little bit of testing.
I was thinking about the worksheets dot add command and what we're going to do here, is we're going to take a look at both Excel and the VBA window at the same time.
So, we can see the VBA window here and you'll be able to see the sheet tabs.
If I just ran worksheets dot add, a couple of times.
So, step through this by hitting [ F8 ], you see that when we add the first worksheet is automatically added to the left of the current worksheet.
So, if we just did worksheets dot add, it would keep adding things to the left and as we think about JB's data, he probably has the first customer alphabetically at the top.
So, I really want to be adding those worksheets to the end and so what we're going to do here instead of just worksheets add, we're going to say after and rather than trying to remember what the last worksheet in the workbook, is I'm going to say worksheets and then specify which worksheet by position.
So, that's simply worksheets count.
So, in this case right now, we have three worksheets.
If I say worksheets dot count that's saying number three and I'll say after the third worksheet.
So, now let's run this a few times and you'll notice down here and the new worksheet sheet twelve will be added after sheet one.
So, that's the trick, in order to be able to add these worksheets.
Now, the other thing you'll notice is that after we add the worksheet.
The new sheet becomes the active sheet.
So in JB's case what we're going to do is we're basically and say...
For each cell in selection.
We're going to add a new worksheet and then, we can rename the active sheet dot name equals cell value and then next cell.
Alright! So, let's go back to the original worksheet, we'll delete all the extra worksheets.
So, we just start out with just sheet one.
I'll select all the customers and then, we'll run our macro, test macro, click [ run ] and sure enough there had added 60 new worksheets in order and named each worksheet with the name from the cell.
Simple a little bit of code, just a few lines of code, have to do some testing there to get worksheets dot add to work the way that we wanted it to.
To add the worksheets to the end instead of always adding it to the left of the active cell.
Thanks to JB for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a question from JB.
JB says, "I have a worksheet with a whole series of customers and I need to create a worksheet for every single customer in the list." He says, "There's hundreds of them.
Is there a way that I could just select a range and you know, say create tabs or something like that." Well, unfortunately there isn't but we're Going to do this with a little bit of VBA, today.
But before I launch into the VBA, I want to do a little bit of testing.
I was thinking about the worksheets dot add command and what we're going to do here, is we're going to take a look at both Excel and the VBA window at the same time.
So, we can see the VBA window here and you'll be able to see the sheet tabs.
If I just ran worksheets dot add, a couple of times.
So, step through this by hitting [ F8 ], you see that when we add the first worksheet is automatically added to the left of the current worksheet.
So, if we just did worksheets dot add, it would keep adding things to the left and as we think about JB's data, he probably has the first customer alphabetically at the top.
So, I really want to be adding those worksheets to the end and so what we're going to do here instead of just worksheets add, we're going to say after and rather than trying to remember what the last worksheet in the workbook, is I'm going to say worksheets and then specify which worksheet by position.
So, that's simply worksheets count.
So, in this case right now, we have three worksheets.
If I say worksheets dot count that's saying number three and I'll say after the third worksheet.
So, now let's run this a few times and you'll notice down here and the new worksheet sheet twelve will be added after sheet one.
So, that's the trick, in order to be able to add these worksheets.
Now, the other thing you'll notice is that after we add the worksheet.
The new sheet becomes the active sheet.
So in JB's case what we're going to do is we're basically and say...
For each cell in selection.
We're going to add a new worksheet and then, we can rename the active sheet dot name equals cell value and then next cell.
Alright! So, let's go back to the original worksheet, we'll delete all the extra worksheets.
So, we just start out with just sheet one.
I'll select all the customers and then, we'll run our macro, test macro, click [ run ] and sure enough there had added 60 new worksheets in order and named each worksheet with the name from the cell.
Simple a little bit of code, just a few lines of code, have to do some testing there to get worksheets dot add to work the way that we wanted it to.
To add the worksheets to the end instead of always adding it to the left of the active cell.
Thanks to JB for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
