Hello!

I have a relatively simply “copy and paste” macro set up in Excel (VBA code below) and it takes maybe 4-5 seconds to run. Is there a way of shortening/compressing the code to make the macro run a bit faster?

Many thanks in advance.

Range(“H13:H17”).Select
Selection.Copy
Range(“H21”).Select
ActiveSheet.Paste
Range(“L13:L17”).Select
Selection.Copy
Range(“L21”).Select
ActiveSheet.Paste
Range(“P13:P17”).Select
Selection.Copy
Range(“P21”).Select
ActiveSheet.Paste

Have you put application.screenupdating = false at the start? Could also try turning off automatic calculation whilst it’s running (provided you don’t need the results of a formula during the code execution), can’t remeber the code, just record switching it on and off from the tools -> options menu.

First thing would be to forget about copy/pasting as such…
so instead of

Range(“P13:P17”).Select
Selection.Copy
Range(“P21”).Select
ActiveSheet.Paste

You could use:

Sheet1.Range(“p21:25”)=Sheet1.Range(“p13:p17”)

Add to that screen updating and auto calc and the macro should run without you even noticing…!

Calc on/off: (& don’t forget to switch screen updating back on at the end)

Sub Calc_Man()
‘Turns on manual calculation’
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
End Sub

***********************
Sub Calc_Auto()
‘Turns on auto calculation’
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub

Nice ideas there – thanks all. I will have a play with those now.

Lot’s of good advice above.

Reading / writing to Excel worksheets is very slow in recent versions of Excel eg 2007 is much slower than 2003 etc. You’re best off trying to mininise the number of times you access the worksheet and do things is bulk eg read in a large range, manipulate it in VBA then write the whole range out again. Inserting / deleting rows has got painfully slow.

Chip Pearson’s website is a very useful resource: http://www.cpearson.com/excel/optimize.htm

Yep, there has been an increase in maximum column/row size from the ‘standard’ 65000, not something that will effect your example, but something to be aware of if you do anything which is for an entire row or column.

StumpyJon – that works great. Seems to have roughly halfed the run time.

Rob Hilton – to turn it back on I need “application.screenupdating = true”?

Lerk – I keep getting an error/debug window, are you sure that is correct as it looks like there is a “P” missing? I have also tried:

Sheet1.Range(“P21:P25”)=Sheet1.Range(“P13:P17”) – and that also returns an error?

Again, thanks everyone for your input.

Oops sorry, just realised what I typed there – Missed out a few key bits.

Try

worksheets(“Sheet1”).Range(“P21:P25”) = worksheets(“Sheet1”).Range(“P13:P17”)

obviously if your worksheet is not called sheet1 you will need to change that bit too. or you could exchange ‘activesheet’ for ‘worksheets(“Sheet1”)’