VBA looping - creating multiple excels

[ericlch16]

Hi

i have built a macro to

1. Open an external excel (50,000 rows)
2. Filter the external excel data based on a filter criteria (10,000 rows left after filter)
3. Loop 10000 time through the filter data in external excel (using xlvisiblecells only)
4. create an individual excel for each of the row from the loop. Each excel has multiple sheets.
5. populate individual excel based on that filter data columns ( i don't copy and paste. I use range transfer for speed )
6. save individual excel

This is working well. But I am wondering if I can improve the speed. it take 25 mins to do and i want to reduce the time. I have 10,000 rows and hence 10,000 workbooks to create and a lot of workbooks window to switch from. Will these improve speed?

• A. Should i copy the filtered data to Thisworkbook and then loop from Thisworkbook instead?
• B. Should I copy the filtered data to Thisworkbook and then copy to an array and then loop from the array ?
• C. Copy filtered data from external excel to an array and then loop from array? (i could not do this because cells are not contiguous )

Which this save time considerably if A and B is done?

 

[ericlch16]

FYI. I did option B. I saved 10 mins. array is much faster than switching between workbooks window. looping in array is faster than workbook.