Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel performance running slow | Excel Discussion (Misc queries) | |||
Excel 2007 painfully slow to open files | New Users to Excel | |||
Workbook running VERY slow | Excel Discussion (Misc queries) | |||
excel running slow when saving | Excel Discussion (Misc queries) | |||
Worksheet Running Very Slow | Excel Worksheet Functions |