Best performance: Many large files to even more smaller files.

[Kalithro]

Not looking for code... just looking for some options.

I have about 30 workbooks to parse though that range in file size of 5mb to 50mb each. Each file has 3 worksheets (2 that I care about). Only one of the worksheets in each workbook has a lot of data (i.e. I know what to do with the smaller worksheets).

For each of the workbooks and the worksheet with all the data I have to split that data in to smaller workbooks. The end result will be something like 30 workbooks split into 100 workbooks.

The new files (100 files/workbooks) will be based on info in one of the columns in the data worksheet. Example (Say col A):

A; B; C; D<cont. to FFish>
f
f
m
f
g
f
m
g
<Repeated many times>

The example above would become 3 files (m.xlsx, f.xlsx, g.xlsx). Key point: The data across each row has tons of formatting that can't be lost (different for each row). Key point: the next workbook (1 of 30... 2 of 30...) may have f data that needs to be appended to a file I have already created (f.xlsx).

Currently I loop though each row and copy that row to the new files.

Would filtering then copying be the fastest?
Would it be faster to copy all the data then sort and find the last relevant row and delete the rest?
How many of the new workbooks can I leave open with the file sizes I’m talking about (32 bit Excel)? Should I just leave them all open until the 30 workbooks are parsed (can I?)?
Any other options?

[Kaper]

1) filtering and copying would be considerably faster than copying row by row. It's probably best available option if you have tons of formatting there.

2) strongly recommended before filtering - sort in filtering column Two major benefits are quicker copying and more important: in case of very disordered files like your sample
f
f
m
f
g
f
m
g
repeted dozens thousands, you may encounter problems with the number of non-contignous areas to be copied. So do sort before filtering.

3) The files are rather big, but probably most (if not all) have less than 524288 rows in a sheet we focus on. Depending on your computer resources (mostly RAM) it may be quicker to join first data from 2 or more files and only then split to smaller.

4) How many workbooks open in parallel and working acceptably well - again it depends on a machine.

[Kalithro]

Thank you for the response. I did 50 large workbooks (avg file size 20 mb) and made 200 smaller workbooks. This completed in about 45 minutes with the row by row copy & paste method. I wrote the row by row part as a separate subroutine. Tomorrow at work I will try the sort & filter method by adding another sub. I may not even have to filter, as of today I also know the number of rows that contain the f, g, m... data. I can simply sort and take rows 2 to x.