How to copy lines of data FAST !

[hhenrion]

Hi experts,

I have a lot of data in one sheet (distributed on many lines, sometimes consecutive, sometimes not) covering each time 60 consecutive rows.

I need to copy them, apply a mathematical formula, and paste the value in a different sheet.

I am copying in a 2 dimensional table, processing, and pasting.
And it take ages...

What is the fastest way of passing lines of 60 rows of data in a table ?

Thanks in advance,
regards,

H.

 

[GlennUK]

How are you doing it at the moment? By hand? By macro?

 

[hhenrion]

MACRO

Hi Glenn,

one piece of information I had forgotten to mention : I use a MACRO.

Rgds,

H.

 

[GlennUK]

In that case post the current macro. There may be something simple to change to improve performance.

 

[hhenrion]

Macro - Text

Here it is :

Sheets("Prod").Select
For i = 1 To 6
For j = 1 To 10
For k = 1 To 60
Prod((i - 1) * 10 + (j - 1) * 4 + 1, k) = Range("F17").Offset((i - 1) * 200 + (j - 1) * 7, k - 1).Value
Prod((i - 1) * 10 + (j - 1) * 4 + 2, k) = Range("F17").Offset((i - 1) * 200 + (j - 1) * 7 + 1, k - 1).Value
Prod((i - 1) * 10 + (j - 1) * 4 + 3, k) = Range("F62").Offset((i - 1) * 200 + (j - 1) * 7, k - 1).Value
Prod((i - 1) * 10 + (j - 1) * 4 + 4, k) = Range("F62").Offset((i - 1) * 200 + (j - 1) * 7 + 1, k - 1).Value
Next k
Next j
Next i
Sheets("Scen").Select
For ij = 1 To 120
For k = 1 To 60
Range("SC_TOP_PCO").Offset(ij, 2 + k).Value = Prod(ij, k)
Next k
Next ij

Rgds,

H.

 

[GlennUK]

Looking at your logic, you are copying contiguous blocks ( 60 cells per row ), from one sheet to another, but are doing one cell at a time instead of a block at a time, and are storing it in an array in between. You can put the values directly in the second sheet without going through an array, and do a block at a time like this:

Sheets("Prod").Select
For i = 1 To 6
For j = 1 To 10
Sheets("Scen").Range("SC_TOP_PCO").Offset((i - 1) * 10 + (j - 1) * 4 + 1, 3).Resize(, 60) = Range("F17").Offset((i - 1) * 200 + (j - 1) * 7, 0).Resize(, 60).Value
Sheets("Scen").Range("SC_TOP_PCO").Offset((i - 1) * 10 + (j - 1) * 4 + 2, 3).Resize(, 60) = Range("F17").Offset((i - 1) * 200 + (j - 1) * 7 + 1, 0).Resize(, 60).Value
Sheets("Scen").Range("SC_TOP_PCO").Offset((i - 1) * 10 + (j - 1) * 4 + 3, 3).Resize(, 60) = Range("F62").Offset((i - 1) * 200 + (j - 1) * 7, 0).Resize(, 60).Value
Sheets("Scen").Range("SC_TOP_PCO").Offset((i - 1) * 10 + (j - 1) * 4 + 4, 3).Resize(, 60) = Range("F62").Offset((i - 1) * 200 + (j - 1) * 7 + 1, 0).Resize(, 60).Value
Next j
Next i

Let me know if it doesn't work ( done in a hurry ).

 

[hhenrion]

Thxs

tanks a lot,

regards,

Hervé