VBA - How to speed up inserting and deleting sheets?

[knifey]

Hi,
I have a long complex macro that is acting very strange and was after some advice.

This macro does:
1.) Loops through 3000 or so rows (which will grow).
2.) On each row it needs to filter for a code in another sheet, copy filtered list, paste to a new sheet, sort by earliest date first, filter for date and sum qty, filter remaining dates one by one and sum qty's (phew)

Due to this it needs to delete and recreate a temp sheet for every one of the 3000 rows (i.e. inside a loop). This is taking about 1.5 seconds per row (x 3000 = 1 very long running 30 minute macro).
Is there any way I can speed this up (I already have screenupdating=false and calculation=manual)?
The strange thing is that every so often the macro runs in about 2 minutes. It will run very fast for a few consecutive tries. Then will revert to taking 30 minutes????? Any ideas?

Any help or advice would be much appreciated.
Thanks,
K

 

[strongm]

>This is taking about 1.5 seconds per row (x 3000 = 1 very long running 30 minute macro).

You might want to put those numbers through a spreadsheet ... ;-)

 

[SkipVought]

Hi,

Depends on how you have coded your procedure.

faq707-4105

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

1.) Loops through 3000 or so rows (which will grow).
2.) On each row it needs to filter for a code in another sheet, copy filtered list, paste to a new sheet, sort by earliest date first, filter for date and sum qty, filter remaining dates one by one and sum qty's (phew)

There might be a better approach.

Rather than telling us HOW you decided it ought to be done, why not explain WHAT it is that you ULTIMATELY need to accomplish?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

I've gone for the obvious solution of removing the temp sheet by sorting by date first, then filtering!

Cheers,
K
P.S. Nice catch strongm (the loop skips many rows that are not needed).