Need to optimize code for faster processing

[drewship]

Hello all. I think I finally have completed the project I have been working on but since it contains code from myself as well as several other folks from the Forum, it could use an expert to optimize it so it processes faster. I added a popup that displays the total processing time of the main code block and it currently runs on the attached file in .4ish seconds. The problem is running this on my production data currently creates 544 individual sheets and each sheet is processed before adding the next sheet, so the current processing time is almost 19 minutes on a decent workstation with 4GB RAM. The sorting of the sheets and the links on the 2_Summary sheet probably don't take too long but if they can be optimized that would be great too. Thanks in advance.

Andrew

EDIT - I added the timer to the sorting code and it takes almost 23 minutes to sort, so that needs optimization too!

[joe31623]

http://www.cpearson.com/excel/optimize.htm

[drewship]

Thanks joe31623. I will take a look and see how much of that I can use.

[JOHN H. DAVIS]

Another point you may consider is to avoid selecting object in order to take action on them. For instance in your procedure "CreateNewSheet".

This section:

Please Login or Register  to view this content.

Can be rewritten like this:

Please Login or Register  to view this content.

Reference:

http://dailydoseofexcel.com/archives...-and-activate/

https://blogs.office.com/2009/03/12/...est-practices/

[drewship]

Thanks John! I could get

Please Login or Register  to view this content.

to work as

Please Login or Register  to view this content.

but could not figure out how to combine it further.

[drewship]

Please Login or Register  to view this content.

gives me a "Copy method of Range class failed" error. Guessing it has to do with this section of code copying formulas, so I can't combine it?

EDIT -

Please Login or Register  to view this content.

gives me "Object doesn't support this property or method"...probably because it is using an offset to reference a cell.

[drewship]

Significant improvements just by adding

Please Login or Register  to view this content.

to a couple places in the code block that creates the sheets (14 minutes) and even bigger improvement using it in the sorting section (4 seconds)!!! Still working my way through the document but thanks again Joe!!!

[JOHN H. DAVIS]

For the first one (doesn't work with Paste Special):

Please Login or Register  to view this content.

For the second one, maybe:

Please Login or Register  to view this content.

[drewship]

Thanks John. Did some testing yesterday without condensing the 2 above (which I started fixing today) and it seemed to actually take longer on my production file. Going to try running it a few times and changing things around to see what combination works the best.

[drewship]

Please Login or Register  to view this content.

is consistently slower than

Please Login or Register  to view this content.

although that seems counter intuitive. Just turning off calculations is faster than also changing this code.

[JOHN H. DAVIS]

Please Login or Register  to view this content.

is consistently slower than

Please Login or Register  to view this content.

although that seems counter intuitive. Just turning off calculations is faster than also changing this code.

This way is probably faster for that piece of code. Of course my suggestions are not meant to ignore Turning of Calculations, and ScreenUpdating, but in addition too.

Please Login or Register  to view this content.

[drewship]

Thanks John. I will tinker with what you have provided and the other suggestions. The last one still increases the processing time and additionally, it messes up something in the code so the Conditional Formatting on 2_Summary column A does not show the colors.

[drewship]

Just adding the updated file as it is working correctly and as fast as I have been able to make it. Try to condense some of the code as has been suggested above and see if your results are different than mine.