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

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 ?

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

Awesome thank you!

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

Thanks for the help.

Mike

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

Try this one.

Please Login or Register  to view this content.

Works like a charm!

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

Mike