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