Macro Create Sheets from Cells

Learn Excel from MrExcel Podcast, Episode 2082: Create Worksheets Macro

Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.  Well, last week I was in Bloomington and Indianapolis, Indiana doing two seminars there and Pam from the Indy seminar said that she has this worksheet, and for each cell in Column A she wants to create a new worksheet and then I made this part up, put the corresponding cell from Column B in Cell B1.

I mean currently, Pam noted that she is doing this in a very clever way, how to create all those worksheets but it's still a hassle.  She goes to Insert, Pivot Table, OK and then from that new Pivot Table, she puts the Account in the Filters area and then Data in the values area like that.  And then, she goes to Options, Show Report Filter Pages, Show all pages of account, and BAM!  She gets the sheets aims like she wants.  Now that the data is in the wrong spot, she actually says at this point she just goes through groups everything and then deletes those pivot tables like that, okay?  So, that's the method she's using right now and I want to see if I can do something faster of that worksheet.

So the way we're going to do this is you’re going to have to pre-select the sheets, pre-select the sheets.  Also hey, if you've never done macros before, do Alt+T for Tom, M for Mary, S for Sam and change from the first choice to the second choice.  Also really, really important, your file I guarantee is saved as .xlsx.  It will not work if you leave it that way, you have to do File, Save As and choose this Excel Macro-Enabled Workbook.  That top one is the default and that top one is the horrible file format that is not allowed to use macros. 

Alright, so we're going to select that data Alt+F11, opens up the VBA Editor.  If you've never done VBA before, you’re going to get this ugly screen here.  Will say Insert, Module and there's our new module and you're going to type this code: the word Sub CreateSheets().  As soon as you type that they're going to put an End Sub but between the two of those we’re going to say For Each cell in Selection.  We're going to Set Worksheet and really we should probably do this Dim WS as Worksheet.  Want to set worksheet = Worksheets. Add.  Now, Worksheets.Add needs to know where we want to add it and what I want to do is I just want to add this worksheet after the last work she's in the workbook.  So, I'm going to say how many worksheets do we have now.  And here let's just try, let’s say: Debug.Print worksheets.Count.  Alright, it says we have 4, let's go back to Excel and check that out.  1 2 3 4 so we’re        going to be adding it after the 4th sheet.  So after the wrap-up, we should see a new worksheet and that worksheet is where we're going to put the data for account 100. 

Alright now, WS.Name = cell.Value and then we decided that we’re going to take whatever is to the right of the Account: the Data.  And we’re going to take that Data and put it in cell B1 of the brand new worksheet that we just created.  So from the cell, right, for each cell we're going to Offset 0 rows 1 column over and grab that Value, alright?  So we have our macro: File, Close and Return to Microsoft Excel and then we'll press Alt+F8.  Alt+F8, CreateSheets, will go to Options.  Boy, what’s going to be a good one?  Ctrl+Shift+W.  W for worksheets.  So I hold down Shift+W in there, click OK, and we can click Cancel.  Alright, select those cells and then Ctrl+Shift+W.  Watch right here after Wrap Up – BAM!  It has them on each one.  It got the data from each individual sheet.  Cool, cool way to go. 

Being able to create many worksheets on the Fly and of course, it would work for far more than that.  Let's do a =ROW*2 copy this down, down, down, down, down and select all of those - Oh wait, we got to put some data over there.  Let's fill it with 2, alright.  And then Ctrl+Shift+W.  BAM! Oh, I already used one Sheet 49.  But look, we were all the way up to the near, the end fast fast way.  How to create a lot of different worksheets on the Fly.   

Alright, topics in this episode:  Pam from Indy wants to create a worksheet for each account number in Column A.  Currently, she is using the Show Report Filter Pages of Pivot Tables.  She's looking for a faster way, alright?  So we’re going to save the workbook as XLSM.  We're going to go Alt+T M S and set to the second choice, not the first choice.  Alt+F11, now we're in VBA, Insert Module, Type the code as shown, Alt+Q to return to Excel, Alt+F8, select the macro name and click Options, assign to Ctrl+Shift+W.

Hey, I want to thank Pam for being in my seminar in Indianapolis.  We had a great time there, good group.  And I want to thank you for stopping by.  We’ll see you next time for another netcast from MrExcel.