create separate files from master

Hi all,

I have been wracking my brain about what to do regards the below scenario.

I have a master list of data that i need to break up into 12 individual files and belive a macro to be the best way to progress.

Basically this file contains the following:

reference no, number(6)
mins worked, number(5)
date input,number(8) {format yyyymmdd}
location number, number(2).

Its this location number that will be the decisive factor when creating the 12 output files. We have 12 locations, and need a file for each location. Please see sample data below:

821977004152007030301
821985003902007030301
821276004802007030302
821349003162007030302
875104003902007030302
820881002402007030303
820921002402007030303
818186003902007030304
820687003402007030304
820717004602007030304
818968003252007030305
818984002402007030305
818992003902007030305
821861003602007030306
821896004852007030306
818135004282007030307
819621003202007030307
121754006202007030308
218065000402007030308
299121004452007030309
756581004002007030309
819522003052007030310
819700004602007030310
221309005292007030312

the output varies from week to to week (there may be 120 rows on week x, but 145 on week y) so where the location changes is not consistent.

Does anyone have any ideas on how I would go to write a macro based on the info above? If you need me to provide more info, please let me know.
Thanks,

You only need to split records in 12 files (text files) based on last two bytes of each record (location) or you prefer to export data in 12 xls files with the same criteria?

Regards,
Antonio

Thanks for response guys,

Antonio, I only need to split the records out into 12 text files based on last two bytes (location).

Carim, i will check out the link and see if it helps in this case.

Cheers...

This code may be what you need:

Please Login or Register  to view this content.

You can run this code from Excel VBA.

Regards,
Antonio

Hi Antonio,

thats exactly what I am looking for. Great code...thanks for that.

I have a little tweaking to do (as the output file names change from week to week). But the core problem is resolved, thanks to your code.

Cheers.

Jo.

Glad to know you fixed your problem.

About to change output file name week by week tell me and I will try to hekp you.

Regards,
Antonio

Hi Antonio,

Im out of the office for this week and early next week. But will try to check in from home..

Basically, the file format that changes each week is for the week number
e.g. on week 5 I would have files named:

bm0105.txt
bm0205.txt
...
...
...
bm1205.txt

'bm' remains constant in the name, 01 - 12 represents the location and 05 represents the week number.

Thanks again for you help, I appreciate it.

Jo.