+ Reply to Thread
Results 1 to 9 of 9

Macro/VB Code to Divide Worksheet into several New Files By Column

  1. #1
    Registered User
    Join Date
    04-29-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    5

    Macro/VB Code to Divide Worksheet into several New Files By Column

    Hi,

    I'm brand new to using these features in excel. i have why I think is a relatively simple issue I've been unable to figure out.

    I have a we form that students complete giving a lot of information about themselves. This form spits out either XML or a CVS of all of this data. I've alwys imported this data into Access where we can process it an run reports. My issue is that the new form creates a lot of column and the new file it spits out is about 500 columns, way to much for a single access table.

    Basically this can be solved by dividing up the CVS/XLS but I have to do this daily so I'd like to do it with a script.

    So in simple terms lets say:
    Column A - Student Number
    Column B-Z - Is Demographic Info
    Column AA-AZ - Is Course Info
    Column BB-BZ - Is Housing Info

    I want to spit out three files, each of which starts with Column so I can use the ID as a key in access. So my 3 files woud be:

    Demographic Info: which would have Column A and B-Z
    Course Info:which would have Column A and AA-AZ
    Housing Info:which would have Column A and BB-BZ

    Any help would be much appreciated.

    Mike

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    We would need to reference the source file - is it on a spreadsheet or are you wanting to get it from the source - either way we'd need code or a book name and format.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,117

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    Also, can you better describe what spitting out a file means to you. Do you want thee new Excel workbooks each with one worksheet with the data columns you've defined or maybe three CSV files ?

  4. #4
    Registered User
    Join Date
    04-29-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    Thanks for the help.

    Attached is the CSV that is exported.

    I want the separate workbooks each with one worksheet with the data. It can be xls or csv.

    So from this attached file I would like 6 separate workbooks all of which have contain column D in the worksheet.

    File 1:
    A-D

    File 2:
    D, M-AR

    File 3:
    D, AT-HZ

    File 4:
    D, IB-JX

    File 5:
    D, JY-MC

    File 6:
    D, MF-RK

    Mike
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,117

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 04-29-2013 at 08:56 PM.

  6. #6
    Registered User
    Join Date
    04-29-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    Awesome thank you!

    I'll try it when I get back to work in the AM and report back.

    Thanks for the help.

    Mike

  7. #7
    Registered User
    Join Date
    04-29-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    The code starts to work great, then after the first file I get a "Run Time Error 7 - Out of Memory". WHen I hit the debugger the attached is displayed.

    Any ideas?

    Mike
    Attached Images Attached Images

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,117

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    Try this one.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-29-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro/VB Code to Divide Worksheet into several New Files By Column

    Works like a charm!

    Thank you so much AlphaFrog, you've saved the day!

    Mike

+ 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