USE ALL CODES in 1 code

[fakhteh]

hi can any body help me
i run allsheets code as below and it takes long time for example i had 800 sheets and it takes time about 4 hours of me
i think it can be shorter and better
please help me to gather all in one code

Please Login or Register  to view this content.

Than k you so much

[Kaper]

I'd take such measures to make it quicker
in AllSheets

Please Login or Register  to view this content.

Swithing off automatic recalculation and calling

Please Login or Register  to view this content.

only after

Please Login or Register  to view this content.

is filled with formula, then after

Please Login or Register  to view this content.

is filled with formula
If you don't need formulas in M and R columns I'd convert (after recalculation) these columns formulas -> to values, like:

Please Login or Register  to view this content.

I'd not use

Please Login or Register  to view this content.

after each graph is prepared (it's 800 large workbook saving actions!)

and as for errors searching and replacing - if you could allow all errors (not only #DIV/0!) to be replaced with "" then

Please Login or Register  to view this content.

PS. Just putting all actions into one sub (which is perfectly possible) will save you no noticeable time amount. But the proposed ammendments could save a lot. Of courrse 800 sheets (with 800 graphs!) is still very large file and some time will be needed to process it.

[fakhteh]

Hi Mr Kaper
please use this workbook for running your code and compare its result with my previous code (i should say that it is not my code some one else wrote it and i appreciate him or her same as you)
thank you

[Sintek]

800 sheets - 4 Hours...
As per Kaper suggestions...Down to 8 seconds...
I don't see the need for Error delete code...Perhaps formula need to be adjusted to ensure no errors in formula...
can only see once a sample file is uploaded containing such errors...

Please Login or Register  to view this content.

[fakhteh]

Hi Mr Sintek
your code could be acceptable if the DivError not exist so please check your previous code and put this part (ReplaceDivError) in it .
i attached an example for this error.
thank you

[Sintek]

if you want to clear errors just change formula...

Please Login or Register  to view this content.