Split workbook based on column

[Sindoh]

Hi, I am looking for help to make a macro that copies the contents of one workbook into multiple .csv files

The main workbook has 1000 collumns, each of which needs to be copied into a seprate .csv file located in the same directory i.e. copy column 1 into 1st csv file, column 2 into 2nd csv file, column 3 into 3rd csv file and so on

For each .csv file I want the data to be copied into column 6, I already have the 1000.csv files created in the directory

I have been looking online but I am only finding code which splits workbooks into sheets, I am abit of a novice when it comes to coding, I am happy to modify it but struggle writing from scratch

Thanks very much for your help

[Sindoh]

Is it worth uploading an example workbook? Not sure how complicated the macro needs to be

[arlu1201]

How will the code know which column should go to which file?

[Sindoh]

I need it so that the macro copies the 1st column into the first csv spreasheeet, 2nd column into the second csv spreasheet

The way I see the seudo code it would be something like

Open mainworkbook, copy 1st column
Open first csv file
Paste column from main workbook into 6th column
Close first csv file
Open mainworkbook, copy 2nd column
Open second csv file
Paste column from main workbook into 6th column
Close second csv file

and so on...... is that doable?

Thanks for replying

[Sindoh]

is that clear enough and more importantly doable in a macro?, forgot to mention the .csv files are numbered like 10001_scen_dly.csv 1000_scen_dly.csv, 10003_scen_dly.csv etc

Do I need to upload a sample workbook and csv?

[arlu1201]

Yes you can attach the sample files.

Are the csv files named as per the excel file column header? Or can the macro pick up any file from the folder and copy paste the column ?

[Sindoh]

Attached is a sample .csv file (converted to xls for upload)

Here is a list of the csv files located in the below directory(exceeds forum limits)
C:\Users\Dave\Desktop\Data\Site1\

http://www.filedropper.com/filenames_1

The CSV files are not named per the header, though they need to be copied sequentially i.e. 1-2-3 etc(Do i need a macro to copy the .csv name into the header row?)

The master workbook can be created by making a xls file with 1000 columns and 10000 rows and placing it into the same folder as the .csv files, I can link a file upload if you want

If you dont want to download off the file share site I can email you (PM me details)

Thanks very much for your assistance

[arlu1201]

What if the macro picks up the wrong file and copies the column over? Are there any risks associated with it?

Usually i have seen that the macro picks up the files in the order in which they are present in the folder. But what if you make an edit to one of the un-opened files? This will disrupt the order and there are chances the wrong file will be picked up.

[Sindoh]

There are risks associated with copying the wrong file over,

In the end, the .csv files will be made up of two columns from two different workbooks, in my original idea of the macro, the master workbook was made up of two sheets, the first containing columns which will be copied into column 6 in each .csv and the second containing columns which will be copied into column 14 in each .csv - However I think this would make the macro over complicated

I settled for just using one master workbook at a time and just running the macro twice after altering the code to pick up the new columns - As a result it is essential that the order is preserved. Is there no way to make sure the macro picks out the correct .csv files sequentially, their naming convention means they are stored in the correct order in the directory itself

The first few files are stored as

00001_scen_dly.csv
00002_scen_dly.csv
00003_scen_dly.csv
00004_scen_dly.csv
00005_scen_dly.csv
00006_scen_dly.csv
00007_scen_dly.csv
00008_scen_dly.csv
00009_scen_dly.csv
00010_scen_dly.csv
00011_scen_dly.csv
00012_scen_dly.csv
00013_scen_dly.csv
00014_scen_dly.csv
00015_scen_dly.csv
00016_scen_dly.csv
00017_scen_dly.csv
00018_scen_dly.csv
00019_scen_dly.csv
00020_scen_dly.csv
00021_scen_dly.csv
00022_scen_dly.csv
00023_scen_dly.csv
00024_scen_dly.csv
00025_scen_dly.csv
00026_scen_dly.csv
00027_scen_dly.csv
00028_scen_dly.csv
00029_scen_dly.csv
00030_scen_dly.csv
00031_scen_dly.csv
00032_scen_dly.csv
00033_scen_dly.csv
00034_scen_dly.csv
00035_scen_dly.csv
00036_scen_dly.csv
00037_scen_dly.csv
00038_scen_dly.csv
00039_scen_dly.csv
00040_scen_dly.csv
00041_scen_dly.csv
00042_scen_dly.csv
00043_scen_dly.csv
00044_scen_dly.csv
00045_scen_dly.csv
00046_scen_dly.csv
00047_scen_dly.csv
00048_scen_dly.csv
00049_scen_dly.csv
00050_scen_dly.csv
00051_scen_dly.csv
00052_scen_dly.csv

I can copy the other 9000+ if you can't access the txt file

[arlu1201]

In the end, the .csv files will be made up of two columns from two different workbooks, in my original idea of the macro, the master workbook was made up of two sheets, the first containing columns which will be copied into column 6 in each .csv and the second containing columns which will be copied into column 14 in each .csv - However I think this would make the macro over complicated

If any random file can be picked up and the data from the 1st column be copied and immediately the 2nd column, then this code will be much simpler.

However, since your files are stored with numbers at the beginning like 000045, 000055, etc...then the macro can pick it up as well. And it can also copy the 2 columns for you in one macro. You do not need 2 macros then and you do not even need to run it twice.