Macro Running Painfully Slow!

[bigV]

I have written a simple Macro that copies and pastes one excel line 1000
times. For some reason it takes forever on my computer (~5 minutes). Any idea
why?

Any suggestions much aprpeciated!!!

Here's the code:

Sub Test()
'
'
Application.Calculation = xlCalculationAutomatic

Set Result_Range = Range("d11")
n = 0
While n < 1000

Range("d7:it7").Select
Selection.Copy
Result_Range.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Set Result_Range = Result_Range.Offset(1, 0)
n = n + 1

Wend
End Sub

 

[Bernie Deitrick]

It takes forever because you're looping. To fix it, don't loop:

Range("d7:it7").Copy
Range("d11").Resize(1000).PasteSpecial xlValues
Application.CutCopyMode = False


HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

try this to paste the value from f1:i1 to cells f2:i5

Sub copyvalues()
Range("f2:i5").Value = Range("f1:i1").Value
End Sub

 

[Tim879]

Try adding this line after your application.calculation.....

application.screenupdating = false

then before your end sub line add application.screenupdating = true

 

[bigV]

Bernie,

The reason I am looping cause row 7 contains randomly generated numbers and
i need them to 'refresh' each time so that each of the 1000 lines has
different values. Any ideas how to incorporate it?

Thank you for help.
Voytek

 

[Bernie Deitrick]

Voytek,

If the randomly generated numbers are based on formulas in row 7, then you
could do a copy and paste prior to converting to values:

Range("d7:it7").Copy Range("d11").Resize(1000)
Application.CalculateFull
With Range("d11:it1010")
.Copy
.PasteSpecial xlValues
End With
Application.CutCopyMode = False

If the randomly generated values are generated elsewhere in your looping,
then you would be better off converting that to formulas.

HTH,
Bernie
MS Excel MVP