+ Reply to Thread
Results 1 to 13 of 13

Make new worksheets from data in workbook

  1. #1
    Forum Contributor
    Join Date
    04-03-2009
    Location
    USA, California
    MS-Off Ver
    Excel 2007
    Posts
    385

    Make new worksheets from data in workbook

    I have a workbook that contains information for around 50 item numbers every day and would like to know if there is a way for excel to make a new workbook for each item number? Basically you have Cell A2 with the Item Number and Cells B2,C2,D2,E2 all contain additional information needed. The goal would be for say item 1 which in the sample workbook spans from A2:E27, all that information should be copied to a new workbook named Item 1, then excel should advance and do this same process for the next item number.

    Please someone help me, this has been a thorn in my side for some time.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    762

    Re: Make new worksheets from data in workbook

    HI
    In the macro codes, change the filepath from "C:\" to where you wish the split files saved and run the macro.
    Ravi
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-02-2010
    Location
    Mexico
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Make new worksheets from data in workbook

    There is an excel add in that does exactly what you want, you can download it from:
    http://www.datapigtechnologies.com/freeware.htm
    Hope this help

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make new worksheets from data in workbook

    Here's my stock macro for this, I've tweaked it for your use. Just set the fPathSave at the top the correct path for the files to be saved.
    Please Login or Register  to view this content.
    ===========
    How/Where to install the macro:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet

    The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Contributor
    Join Date
    04-03-2009
    Location
    USA, California
    MS-Off Ver
    Excel 2007
    Posts
    385

    Re: Make new worksheets from data in workbook

    Thanks all, and thanks JB your code is working well...now if only I could filter the information before the parse macro is run. I will play with it some more tonight/ tomorrow and might post a topic on it if I can't get it working.

  6. #6
    Forum Contributor
    Join Date
    04-03-2009
    Location
    USA, California
    MS-Off Ver
    Excel 2007
    Posts
    385

    Re: Make new worksheets from data in workbook

    Only one small issue I've run into JB: When the information is copied the row width information is lost as well as cell formating. Is there a way to preserve this information? If not it isn't hard to modify it at the time the document is printed.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make new worksheets from data in workbook

    You can probably spot the line of code that is "autofitting" the columns, I always do that to make it tidy. You can change that line:

    Please Login or Register  to view this content.

    The xlPasteAll directly above that insures the formatting travels with the paste command, so that should already be preserved.

  8. #8
    Forum Contributor
    Join Date
    04-03-2009
    Location
    USA, California
    MS-Off Ver
    Excel 2007
    Posts
    385

    Re: Make new worksheets from data in workbook

    When I change the line of code you mention, the parse macro breaks, it copies the first item number fine but after that everything is blank. Any other ideas? Also if I change the page format on the origin sheet such as borders and the like will those formats be copied over to the new sheets? After all it is much faster to change that sort of thing one time then do it every time I need one of the sheets printed.

    Please Login or Register  to view this content.
    this is what I came up with, which is faster then the other way I was doing it, but not as fast as before I made changes to it.
    Last edited by randell.graybill; 03-11-2010 at 09:50 PM.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make new worksheets from data in workbook

    You're adding back in "selecting" and such, something humans do that VBA doesn't have to...adding it in always slows things down.

    My suggestion for the pasting columnwidths was specifically aimed at my version of the macro, that command has to come immediately after the original PasteSpecial xlPasteAll command, while Excel still has the copied cells in memory. You did other things then tried it, no dice.

    Please Login or Register  to view this content.

    The date being copied and pasted...thats an xlPasteAll, so everything about the cells formatting should travel with.

  10. #10
    Forum Contributor
    Join Date
    04-03-2009
    Location
    USA, California
    MS-Off Ver
    Excel 2007
    Posts
    385

    Re: Make new worksheets from data in workbook

    Ok I'll give it a shot JB; thanks again for your continue help and patience.

  11. #11
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make new worksheets from data in workbook

    No problem.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  12. #12
    Forum Contributor
    Join Date
    04-03-2009
    Location
    USA, California
    MS-Off Ver
    Excel 2007
    Posts
    385

    Re: Make new worksheets from data in workbook

    Just thought I'd post this update in case this information would be helpful to someone else. The problem was in the paste special was being done in the wrong order.

    Please Login or Register  to view this content.
    Pasting of the xlPastecolumnWidths must take place before pastespecial xlPasteAll

  13. #13
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make new worksheets from data in workbook

    Great tip, thanks Randell.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1