Uma wants to create many worksheets in a new workbook that are named after values in range B2:B20. Episode 1072 shows a VBA macro that makes all of this possible.
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.
So, how we're going to analyze this, as well, lets fire up a pivot table.
Let's see if you can solve this problem.
All right, welcome back to the MrExcel netcast. I'm Bill Jelen.
Great question today, sent in by Uma.
Uma has a list of names in column B, and Uma says for every name, she wants to create a worksheet in a new workbook and name the worksheet after that name.
All right now, yesterday we solved Harry's problem using a recorded Macro.
This is a Macro that we're not going to be able to record.
And so, let's switch over to visual basic, and we'll talk about this Macro that I wrote.
Still a short little Macro about 20-25 lines.
Up here, I'm defining some object variables, WBT and WBN, those are workbooks.
WBT is this workbook, the workbook that we're starting from and WBN is a new workbook that i'm going to create.
WST is the original worksheet, and WSN is the worksheets that i'm going to create.
So we're up here. I say hey, WBT = ActiveWorkbook and WST = ActiveSheet I know, as I go through, I want to add those sheets at the end of the workbook, and so, I set up a little variable here called Ctr, that tells me how many worksheets I'm doing.
And then this is the part of the Macro recorder will never ever get the loop.
We're going to say, For Each Cell In Selection.
That gives us, makes this Macro very, have a lot of flexibility. We can select any range of cells and it will loop through all of those cells.
And, first I check and see if the Ctr = 1 If it is, then I use Workbooks.Add(xlWBATWorksheet).
That says, hey create a brand new workbook with a single worksheet.
No matter, what their settings are, how many sheets in a new workbook.
You know that varies from computer to computer.
Some people have it at 3, some people have it at 1.
You know, I don't care what that setting is. I want a single worksheet, and define WSN as that first worksheet.
If it's past the first cell, then I just want to use the existing workbook.
WBN.Worksheets.Add (after : =WBNWorksheets(Ctr): and again assign at the variable name WSN.
Now a couple of things here WSN.Name = cell.Value I wrap that in On Error Resume Next and On Error GoTo 0 That's just in case, the cell contains a legal character.
An exclamation point or something that you can't have it a name.
Atleast it will keep running, and then in cell A1.
That's row 1, column 1, put the name of the worksheet.
Just, I don't know, if how many needed to add, but I added it in.
Finally, keep track for our counter.
After I'm done with the Macro, make sure that I go back and select the first worksheet.
So hey, let's try this out.
I'm just going to select some cells here, and we'll go to our Macros here.
Choose Makeworkbook and click Run.
And, what we're going to see is, very quickly there is a brand new workbook, with all of the different sheet names from our original worksheet. Great way to go.
You know, takes a few lines of code, but once you have those codes, code, you can then, very quickly create a workbook with new worksheets based on the cells.
I want to thank Uma for sending that question in.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Basically, we start out with massive amounts of data.
So, how we're going to analyze this, as well, lets fire up a pivot table.
Let's see if you can solve this problem.
All right, welcome back to the MrExcel netcast. I'm Bill Jelen.
Great question today, sent in by Uma.
Uma has a list of names in column B, and Uma says for every name, she wants to create a worksheet in a new workbook and name the worksheet after that name.
All right now, yesterday we solved Harry's problem using a recorded Macro.
This is a Macro that we're not going to be able to record.
And so, let's switch over to visual basic, and we'll talk about this Macro that I wrote.
Still a short little Macro about 20-25 lines.
Up here, I'm defining some object variables, WBT and WBN, those are workbooks.
WBT is this workbook, the workbook that we're starting from and WBN is a new workbook that i'm going to create.
WST is the original worksheet, and WSN is the worksheets that i'm going to create.
So we're up here. I say hey, WBT = ActiveWorkbook and WST = ActiveSheet I know, as I go through, I want to add those sheets at the end of the workbook, and so, I set up a little variable here called Ctr, that tells me how many worksheets I'm doing.
And then this is the part of the Macro recorder will never ever get the loop.
We're going to say, For Each Cell In Selection.
That gives us, makes this Macro very, have a lot of flexibility. We can select any range of cells and it will loop through all of those cells.
And, first I check and see if the Ctr = 1 If it is, then I use Workbooks.Add(xlWBATWorksheet).
That says, hey create a brand new workbook with a single worksheet.
No matter, what their settings are, how many sheets in a new workbook.
You know that varies from computer to computer.
Some people have it at 3, some people have it at 1.
You know, I don't care what that setting is. I want a single worksheet, and define WSN as that first worksheet.
If it's past the first cell, then I just want to use the existing workbook.
WBN.Worksheets.Add (after : =WBNWorksheets(Ctr): and again assign at the variable name WSN.
Now a couple of things here WSN.Name = cell.Value I wrap that in On Error Resume Next and On Error GoTo 0 That's just in case, the cell contains a legal character.
An exclamation point or something that you can't have it a name.
Atleast it will keep running, and then in cell A1.
That's row 1, column 1, put the name of the worksheet.
Just, I don't know, if how many needed to add, but I added it in.
Finally, keep track for our counter.
After I'm done with the Macro, make sure that I go back and select the first worksheet.
So hey, let's try this out.
I'm just going to select some cells here, and we'll go to our Macros here.
Choose Makeworkbook and click Run.
And, what we're going to see is, very quickly there is a brand new workbook, with all of the different sheet names from our original worksheet. Great way to go.
You know, takes a few lines of code, but once you have those codes, code, you can then, very quickly create a workbook with new worksheets based on the cells.
I want to thank Uma for sending that question in.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
