'.Copy Destination:=' temporarily freezes multiple instances in Excel 2010

[Poniente]

Dear all,

I'm running vba code in multiple instances of Excel and I recently migrated from 2003 to 2010.

Something odd happens: processor load of _all_ processors occasionally falls well below 15% if any of the running instances uses the copy code with the format:

Range("A1:Z1").Copy Destionation:=Range("A2:Z4000")

Together with the fall in processor load, all instances of Excel temporarily freeze for a few seconds, even though the other instances are not executing this kind of code, and should keep running at near 100% processor load.

The code does not cause Excel to crash, but it does affect overall speed on every time this code is executed in any of the instances. Applications other than Excel are not affected.

To me it sounds like a bug in Excel 2010 (possibly 2007 as well) as the described problems were not happening when running multiple instances in Excel 2003.

This raises three questions for me:
a) Is there anyone out there that can confirm this problem?

And b) Is there a workaround?

For example:
1) Is there any other efficient way to copy a range without using the clipboard?
Or more specific:
2) Is it possible to copy to an instance specific (local) clipboard? (.. so nothing is ever accidentally copied between the multiple instances of Excel)


Thanks in advance for your efforts!

Poniente

 

[Jim Cone]

If it is mostly formulas, requiring calculation then it won't happen instantly.
You could turn off calculation, paste and the turn calculation back on.

Specifying the workbook/worksheet is good practice when copying data. It might even help.

"Destionation" is not spelled correctly.

..Value = .Value is another way, if you don't care about formatting/formulas.
It is faster, but I've had it fail (rarely). When speed is essential (used in a loop),
I back it up by checking for an error and if so pasting the data.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Bingo Card Variety .xls workbook - in the free folder)





"Poniente" <[email protected]>
wrote in message
news:960744.2926.1326628507334.JavaMail.geo-discussion-forums@yqgi18...

 

[Poniente]

Hi Jim,
Solved!

Thanks for taking time Jim. It's mostly formulas. I just found a
solution that is a fast workaround for formulas (a lot faster than
'.copy destination:=').

To whom it may concern:

The source is:
http://excelexperts.com/copy-values-vba


The following simplified code works best for me:
<begin>
Sub PasteFormulasFast(rngSource As Range, rngDesti As Range)

rngDesti.Formula = rngSource.FormulaR1C1

End Sub
<end>

To get things started:
Call PasteFormulasFast(Range("Sheet1!A1"),Range("Sheet1!A2:A4000"))


Thanks again Jim for looking into this!
Poniente