How to Create Worksheets

Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Basically, you start out with massive amounts of data and how are we going to analyze this. Well, let’s fire up the pivot table and see if we can solve this problem. Great. 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 besides for every neme, she want to create a worksheet in a new workbook and name the worksheet after that name. All right. Now yester we solved Terry’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 pace again. We’ll talk about this macro tat I wrote. Still a short little macro about 20 to 25 lines, up here I’m defying some object variables. WBT and WBN, those are workbooks. WBT is this 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 in the worksheets that I’m going to create. So we’re up here, I say WBT is equal to the active workbook and WST is equal to the active sheet. 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 her called counter 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 to 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 of the counter’s equal to one. If it is, then I use ‘Workbooks.Add’ and this very strange word (x1WBATWorksheet). That’s says, “Hey, create a brand new workbook with a single worksheet.” No matter what the settings or how many sheets in a new workbook. You know that varies from computer to computer. Some people have it at three’s some people have it at one. 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 would just want to use the existing work book. WBN.Worksheets.Add. add it after the last workbook and again assign that to the variable name WSN. Now, a couple of things here, WSN.Name=cell.value, I wrapped that in ‘On Error Resume Next’ and ‘On Error Go To 0’.that’s just incase the cell contains some legal character. You know exclamation point or something that you can't have a name. atleast it’ll keep running. And then in cell A1, that’s row one and column one, put the name of the worksheet just I don’t know if when we’ll needed that but I added it in. finally, kkep track for a counter. After I’m done with the macro, make sure that you’ll 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 out macros here. Choose make workbook 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 it takes a few lines or codes, but once you have those codes, 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. We’ll see you next time for another netcast from Mr.Excel. Well thanks for stopping by. We’ll see you next time for another netcast from MrExcel.