Faster code for copy rows

[cfer]

Hi,
At the moment I have a macro when runs takes a long time to run, that I put together.

What I want to be able to do is..

if any cells in Data BU2 = Data BR1

then copy the following cells from Data Sheet to the Finance Sheet

(Data) D to (Finance) C9
(Data) AB to (Finance) D9
(Data) AC to (Finance) E9
(Data) AD to (Finance) F9
(Data) AE to (Finance) G9
(Data) AF to (Finance) H9
(Data) AG to (Finance) I9
(Data) AH to (Finance) J9
(Data) AI to (Finance) K9
(Data) AJ to (Finance) L9
(Data) AT to (Finance) M9

Basically any row in columns Data BU = Data BR1, copy all the (Data) rows above to the rows starting at C9

The amount of rows at times in BU may vary.

Any help appreciatted.

Cfer

 

[ClimoC]

Sorry, your question seems a litle fuddled, because you keep mentioning 'rows' and then giving columns...

What you should be doing is grabbing the row property (as an integer or long, probably long just for safety's sake), and then using that, so you go...

'DS = your Destination Sheet, SS = your SourceData sheet. Use names in quotes for these as you probably already know

dim xrow as long
For each x in range("BU2:BU" & range("BU65000").end(xlup).row)
   If x.value = Range("BR1").value then
         xrow = x.row
         Sheets(DS).Range("D" & Xrow).value = Sheets(SS).range("P" & Xrow)
   end if
next

and of course, if you haven't already, disable screenupdating and calculation(set to manual) to drastically improve the speed

 

[cfer]

Hi ClimoC,

Thanks for the quick reply.

It does read a bit confusing.

Where I have (Data) D, i was meaning to say, if BU2 = BR1, the D would refer to cell D2, AB would refer to AB2 etc, etc

I want to copy D2, AB2,AC2,AD2,AE2,AF2,AG2,AH2,AJ2,AT2, if BU2 equaled BR1 to thew cells in Finance as the list above, in the first post.

If BU3 did not equal BR1 do nothing

If BU4 equaled BR1 copy the equavalent cells in row 4, so on and so on.

Hope this makes more sense.

Thanks again

Cfer

 

[obiron]

posting vales cell by cell is VERY slow.

copy the values you need from a range into an array and then paste the values from the array into a range (or ranges in your case as the cells are not sequential)

If you can move all the values from one range into another range (i.e. the size of the ranges is the same), there is no need to use the interim array.