PDA

View Full Version : How to speed up cut and paste macro code (12-16-09)



stevespauldi
12-16-2009, 07:07 AM
I have vba macro code that does a series of cut and paste operations (not copy and paste) . . . see code below. On the 4th iteration (where x = 4 to start with), the operation gets really slow. On all other iterations, the codes runs fairly quickly.

Is there a way to speed up this operation???
I’ve already tried ‘clearing the clipboard’ and ‘releasing memory’ (code still there).

I’ve also already tried using:
Application.ScreenUpdating = False ‘disable ScreenUpdating
and
Application.Calculation = xlCalculationManual ‘disable AutomaticCalculation

ANY SUGGESTIONS???

Do Until x = 0 'Cut and Paste routine
Range("I16:P17").Select
Selection.Cut
Range("Q20").Select
ActiveSheet.Paste
Range("I18:P18").Select
Selection.Cut
Range("Q26").Select
ActiveSheet.Paste
Range("I19:P19").Select
Selection.Cut
Range("I16").Select
ActiveSheet.Paste
Range("I20:P20").Select
Selection.Cut
Range("I19").Select
ActiveSheet.Paste
Range("I21:P21").Select
Selection.Cut
Range("Q22").Select
ActiveSheet.Paste
Range("I22:P22").Select
Selection.Cut
Range("Q25").Select
ActiveSheet.Paste
Range("I23:P24").Select
Selection.Cut
Range("I17").Select
ActiveSheet.Paste
Range("I25:P26").Select
Selection.Cut
Range("I23").Select
ActiveSheet.Paste
Range("Q20:W22").Select
Selection.Cut
Range("I20").Select
ActiveSheet.Paste
Range("Q25:W26").Select
Selection.Cut
Range("I25").Select
ActiveSheet.Paste
Range("I14").Select
x = x - 1

'MORE TEST CODE HERE *********
Application.CutCopyMode = False 'Clear Clipboard

Dim wSheet As Worksheet
Set wSheet = Sheet1
Set wSheet = Nothing 'release memory
'*******************************

Loop

Thanks for any help,
Steve

Jan Karel Pieterse
12-16-2009, 07:17 AM
Maybe shortening the code to this might help:

Do Until x = 0 'Cut and Paste routine
Range("I16:P17").Cut Range("Q20")
Range("I18:P18").Cut Range("Q26")
Range("I19:P19").Cut Range("I16")
Range("I20:P20").Cut Range("I19")
Range("I21:P21").Cut Range("Q22")
Range("I22:P22").Cut Range("Q25")
Range("I23:P24").Cut Range("I17")
Range("I25:P26").Cut Range("I23")
Range("Q20:W22").Cut Range("I20")
Range("Q25:W26").Cut Range("I25")
x=x-1
Loop

Not sure what the last couple of lines do (with the sheet object), but as they are now you can omit them because nothing is done with the sheet object variable.