Improve the speed of copy-paste

[Guest]

Hi!
I try to copy and paste a range and it's very long since there's a lot of
formula inside. Is there a way to improve the speed. I'm in manual
calculation.
Thanks!
Alex
time1 = Timer
a = Sheets("CALCULATIONS").Cells(5, 3) * 20 + 5
Sheets("PROJ RESULTS STOCHASTIC").Select
Range("C6:C25").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("C26:C" & a).Select
ActiveSheet.Paste
Sheets("PROJ RESULTS STOCHASTIC").Calculate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
MsgBox (Timer - time1) 'Takes 600 sec.

 

[Guest]

Try using

activesheet.calculation = false (at beginning)
activesheet.calculation = true (at end)

or

application.screenupdating = false (at beginning)
application.screenupdating = true (at end)

Or both.

 

[Don Guillett]

I'm a bit confused as to why you first paste the range to the same sheet and
then paste the values to another.
This idea is very fast. It is a one liner that uses NO selections and can be
fired from anywhere in the wb.
Notice that the ranges must be the same size.

sheets("destination").range("a12:x16").value =
sheets("source").range("a2:x6").value

or this idea. Modify to suit.

lc=sheets("source").range("a2").end(xltoright).column
lr=sheets("source").range("a2").end(xldown).row
sheets("destination").range(cells(lr+10,"a"),cell(lr+10,lc)).value = _
sheets("source").range(cells(lr,"a"),cell(lr,lc)).value

 

[Don Guillett]

The continuation char didn't make it. Space and underscore to make one line
two.

sheets("destination").range("a12:x16").value = _
sheets("source").range("a2:x6").value