Splitting one file into many with fewer columns

[andylarsen]

Hi everyone. I don't know if this can be done with or without VBA and a macro. I have a spreadsheet, attached below, which has a number of statistics and then columns with measures of success with 0s or 1s. I need to create multiple spreadsheets, each that have all of the statistics, but only one measure of success at the end. I would just copy and paste, but I need to do this for around 50 files. Any help?

Thanks!

[JBeaucaire]

So the files created would have the following columns:

File 1 = Columns A,B,C,D
File 2 = Columns A,B,C,E
File 3 = Columns A,B,C,F
File 4 = Columns A,B,C,G

Correct?


What would be the naming convention of these files?

File 1 = CareerG.xls
File 2 = CareerMP.xls
File 3 = CareerFGA-G.xls
File 4 = CareerFGP-G.xls
etc...?

[andylarsen]

So the files created would have the following columns:

File 1 = Columns A,B,C,D
File 2 = Columns A,B,C,E
File 3 = Columns A,B,C,F
File 4 = Columns A,B,C,G

Correct?


What would be the naming convention of these files?

File 1 = CareerG.xls
File 2 = CareerMP.xls
File 3 = CareerFGA-G.xls
File 4 = CareerFGP-G.xls
etc...?

You have the right idea, except instead of only A,B,C included on every file, I need every column from A-AO. Naming convention should append the name of the success measure at the end of the file. So:

File 1 = Columns A,B,C...,AM,AN,AOAP
File 2 = Columns A,B,C...,AM,AN,AOAQ
File 3 = Columns A,B,C...,AM,AN,AOAR
File 4 = Columns A,B,C...,AM,AN,AOAS
...

And file names should be
File 1 = pareddown-centers-allmos-post2007-draftednominal.xls
File 2 = pareddown-centers-allmos-post2007-playedInNBA.xls
File 3 = pareddown-centers-allmos-post2007-scored10PPGRookieYear.xls
File 4 = pareddown-centers-allmos-post2007-NBACareerPPG.xls
etc.

Does that make sense?

Andy

[JBeaucaire]

You can't have percentage symbol in file names, so those will be stripped...see BP1...

[andylarsen]

Okay, I'll just find and replace those.

[JBeaucaire]

Give this a try:

Please Login or Register  to view this content.

[andylarsen]

That works exactly as desired, thanks!

A couple of questions:

1. If I want to save them as CSVs, do I simply change the part where it says .xls to .csv? Or is it more difficult than that?
2. Other files I'm using this code will have other numbers of columns that I want to keep, for example, from A:AX. Is changing that a matter of changing the part where it says "Range("A:AO")"? Or do I have to change that column count as well?

Thanks again!

[JBeaucaire]

You would need to change that line of code to indicate the main columns to copy, correct!

But also notice this line of code which is where we add the additional "stat" column:

Please Login or Register  to view this content.

This code was designed to start after your original "main" data columns, column 42 is AP, and it increments up from there. Whatever you change the range of main columns to, edit that 42 to start at the first "stat" column.

To save as CSV, you might just edit this one parameter:

Please Login or Register  to view this content.

[andylarsen]

Thanks again, it all worked great!

[JBeaucaire]

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