MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro Create Sheets from Cells


April 24, 2017 - by Bill Jelen

Macro Create Sheets from Cells

You have a column of text. For each text in the column, you want to insert a new worksheet and name that worksheet for the value in the cell.

Watch Video

  • Pam wants to to create a worksheet for each account number
  • Currently, she is using the Show Report Filter Pages
  • Looking for a faster way
  • Save the workbook as XLSM
  • Check Macro Security with Alt + T M S & set to second level
  • Alt + F11
  • Insert Module
  • Type the code as shown
  • Alt + Q to return to Excel
  • Alt + F8
  • Select the macro & click Options
  • Assign to Ctrl + Shift + W

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2080 to create worksheets macro
  • hey welcome back to the MrExcel
  • NetCast i'm bill gentlemen 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 hey currently Pam noted that
  • she is doing this in a very clever way
  • to create all those worksheets but it's
  • still a hassle she goes to insert pivot
  • table okay and then from that new pivot
  • table she puts the account in the
  • filters area and then data and the
  • values area like that and then she goes
  • two options she'll report filter pages
  • show all pages of account and bam she
  • gets the Sheep 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 your
  • have to pre-select the sheets per you
  • 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 xls X it will not
  • work if you leave it that way you have
  • to do file save as and choose this excel
  • macro and able to work book that top one
  • is the default and at some point is a
  • horrible file format that is not allowed
  • to use macros alright so we're going to
  • select that data alt f 11 opens up the
  • VBA editor if you've never done bvba
  • before you hit this ugly screen here or
  • say insert module and there's our new
  • module and you're going to type this
  • code the word sub create sheets open
  • paren close paren soon as you type that
  • they're going to put an end sub but
  • between the two of those were say for
  • each cell in selection
  • we're in a set worksheet and really we
  • should probably do this dim WS as
  • worksheet we're set worksheet equal to
  • worksheets dot add now where she's not
  • at need 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 gonna say
  • how many worksheets do we have now and
  • here let's just try this let's say
  • debug.print worksheets dot count all
  • right and says we have four and let's go
  • back to excel and check that out one two
  • three four so we going to be adding it
  • after the fourth street so after the
  • wrap-up we should see a new worksheet
  • and network she's where we're going to
  • put the data for account 100 all right
  • now WS name is equal to sell dot value
  • and then we decided that we're going to
  • take whatever is to the right of the cap
  • to data and want 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 so we're going
  • to offset 0 rows one column over and
  • grab that guy all right so we have our
  • macro file close and return to Microsoft
  • Excel and then we'll press alt f eight
  • oh my fate great sheets will go to
  • options point what's going to be a good
  • one control shift w w for worksheets so
  • i hold down shift w in there click ok
  • and we can click cancel all right select
  • those cells and then ctrl shift w watch
  • right here after wrap up bam and as
  • women 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 duel row
  • times to 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 two all right and then ctrl
  • shift w fam already used one sheet 49
  • but look we were all the way up to the
  • near the end fast fast way to create a
  • lot of different worksheets on the fly
  • alright topics in this episode pam from
  • indy wants the credit worksheet for each
  • number in column a currently she is
  • using the show report filter pages of
  • hibbett table she's looking for a faster
  • way all right so where I save the
  • workbook is xlsm we're gonna go all TMS
  • and set to the second choice not the
  • first choice all f11 now we're in vba
  • insert module types of code is shown alt
  • Q to return to excel all f8 select the
  • macro name and click options assigned to
  • control shift w hey I want to thank Pam
  • for being in my seminar in Indianapolis
  • we had a great time there good group and
  • was like you for stopping vials you next
  • time for another neck cast from mr.
  • excel

Download File

Download the sample file here: Podcast2082.xlsm

Title Photo: sign/post/f53b4795-9306-4e3d-a46e / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.