Pasting takes time

[ArnolddG]

Hi,
I have an issue with slow pasting of multiple ranges.

As an example a part of the code in a larger sub:

Please Login or Register  to view this content.

If I paste the formulas to values, the macro runs instantly.
If I leave the formulas as they are, it takes about 5 seconds to paste one worksheet, but I have to copy and paste many worksheets after each other, so 5 sec takes too long.

I have set screenupdating off and calculation to manual.
The worksheet 'sheet' is from another workbook with lots of formulas on it. Recalculation of that sheet takes about 1 full second, BUT... as I have calculation set to manual

How can I speed up the code ?

Thanks for helping me out here...
Arnold

[Jakobshavn]

Is this form any faster?

Please Login or Register  to view this content.

[ArnolddG]

Hi Jakobshavn,

Thanks for your reply.
It actually does run seconds faster. The whole test sheet took 17 sec and that is now reduced to 12 sec.

However I would like to only paste the values and not the formatting along with it.
I am not using xlPasteValues anymore. Is there a way to do that with your code?

Arnold

[Jakobshavn]

Maybe..................What is the "shape" of targetRange? Is it a row or a column or a rectangle??

[ArnolddG]

It is a table where I paste in three ranges of filtered cells with the same amount of rows.
So in my example A3:A1200,C3:C1200,F3:F1200 will be pasted into the table in cell A2 (A1 is the table header).
Range A3:A1200 will go to target sheet A2:A1199
Range C3:C1200 will go to target sheet B2:B1199
Range F3:F1200 will go to target sheet C2:C1199