Copying values between sheets VERY slow...

[ridetoeat]

I'm running a loop that takes the values of certain cells from a range of rows the user has selected on one sheet, and copies them to cells in another sheet in the same workbook.

It works fine, except it is unbelievably slow... nearly 4 seconds per loop.

Obviously there is something terribly inefficient about my code, but I can't imagine what it is. Can someone suggest a more efficient way to accomplish this? The slow part is lines 3 and 4 of the 5 lines of code below. Each of those 2 lines are taking nearly 2 seconds to process, each time through.

The code takes the values from the 4th and 6th columns in the selected rows and places them in Columns A and B, respectively, starting in row 2, of a worksheet named "Cstates", in the same workbook. It needs to work regardless of the number of rows selected, which sometimes may be up to 1000 rows or so, which would take over an hour.

Please Login or Register  to view this content.

Help appreciated!

[elmarko123]

Could you add the example of the data?, ill take a look but want ot see what you mean

[parrynz]

Hi, unless I've misinterpreted what you require you seem to be simply copying data from one sheet to another sheet, column A=>D and B=>F. You are doing this one row at a time where a copy operation can do this in 2 steps, just like if you selected the cells yourself and pasted across. Is there a particular reason why you have done it cell by cell?

Besides a lot of unnecessary looping, if you have a large number of formulas in your work book it may be calculating each time, so that's 2,000 times. This can be avoided by turning off calculation prior to copying, perform the copy then turn it back on so it recalculates only once.

Is that what your after?

[DonkeyOte]

I'd also add to parrynz's point that disabling screenupdating is also advisable when running a cell by cell iteration with values being written to the worksheet (though as outlined already avoiding such iteration is first concern)

For a very brief overview of "best practice" when it comes to coding see this MS blog: http://blogs.msdn.com/excel/archive/...practices.aspx

[elmarko123]

Yeah,

Before code

Applicaiton.Screenupdating = False
Application.Calculation = xlCalculationManual

After code

Application.Screenupdating = True
Application.Calculation = xlCalculationAutomatic

Application.Calculate (not sure if this is needed to force one calcualtion)

For what you have descriped the only thing that i can think of that makes the code run that slow is the fomula.

When possible its best to avoid using formula & doing the calcuations within VBA, personally i find it runs considerably faster, not to mention the file size is ALOT smaller.