Macro Running Painfully Slow!
 

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

Macro Running Painfully Slow!
 

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


"bigV" wrote in message
...
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

Macro Running Painfully Slow!
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bigV" wrote in message
...
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

Macro Running Painfully Slow!
 

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

application.screenupdating = false

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

On May 30, 8:56 am, bigV wrote:
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

Macro Running Painfully Slow!
 

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" wrote:

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


"bigV" wrote in message
...
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

Macro Running Painfully Slow!
 

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



"bigV" wrote in message
...
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" wrote:

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


"bigV" wrote in message
...
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