Copying of consecutive Rows, one at a time and pasting it in onelocation

[shriil]

Hi

I would like to write a procedure in the VB Editor such that the
following operation
(detailed below) can be done at one go.

I have data in each of 12 columns of 20 Consecutive Rows. Ex. From
Rows 23 to 42, Columns D to O
Additionally I have data in Row 7, Column D to O.


Now I have to Copy each of the 20 Rows, one at a time, and paste it
in
Row 8, Column D to O. The First operation occurs when Row 23 is
selected, copied and pasted on Row 8. Based upon the data pasted in
Row 8 along with that already resident in Row 7, a calculated value
is
generated in Row 8, Column P (P8). The value in Cell (P8) is copied
and pasted in Row 9 Column A,i.e., Cell (A9).


In the second operation, Row 24 is selected, copied and pasted on Row
8. Based upon the changed data in Row 8, along with that resident in
Row 7, a calculated value is again generated in Row 8 Column P (P8).
Subsequently the new value in Cell (P8) is copied and pasted in Row 9
Column B,i.e., Cell (B9).


So for each of the 20 Rows pasted at the same location of Row 8, one
by one,
I shall generate 20 calculated values that would be available from
(A9)
to (T9). Depending upon the highest value among these cells, the Row
that was responsible for the same (i.e. one of the Rows between Rows
23 to 42), shall be permanently copied and pasted in Row 8 location.


I hope I have been able to explain it clearly. What I understand that
I have to write the code with a few Do.. While statements, but cannot
fathom how to denote which particular Row to be copied and pasted and
how to increment by one row at a time for the "Copy & Paste"
operation.


Pls Help

 

[Per Jessen]

Hi

I would like to write a procedure in the VB Editor such that the
following operation
(detailed below) can be done at one go.

I have data in each of 12 columns of 20 Consecutive Rows. Ex. From
Rows 23 to 42, Columns D to O
Additionally I have data in Row 7, Column D to O.


Now I have to Copy each of the 20 Rows, one at a time, and paste it
in
Row 8, Column D to O. The First operation occurs when Row 23 is
selected, copied and pasted on Row 8. Based upon the data pasted in
Row 8 along with that already resident in Row 7, a calculated value
is
generated in Row 8, Column P (P8). The value in Cell (P8) is copied
and pasted in Row 9 Column A,i.e., Cell (A9).


In the second operation, Row 24 is selected, copied and pasted on Row
8. Based upon the changed data in Row 8, along with that resident in
Row 7, a calculated value is again generated in Row 8 Column P (P8).
Subsequently the new value in Cell (P8) is copied and pasted in Row 9
Column B,i.e., Cell (B9).


So for each of the 20 Rows pasted at the same location of Row 8, one
by one,
I shall generate 20 calculated values that would be available from
(A9)
to (T9). Depending upon the highest value among these cells, the Row
that was responsible for the same (i.e. one of the Rows between Rows
23 to 42), shall be permanently copied and pasted in Row 8 location.


I hope I have been able to explain it clearly. What I understand that
I have to write the code with a few Do.. While statements, but cannot
fathom how to denote which particular Row to be copied and pasted and
how to increment by one row at a time for the "Copy & Paste"
operation.


Pls Help

Hi

Try this.

Sub test()
Dim tRange As Range
Dim r, c
Dim testval As Long
Dim ColOff As Long
Const DestRange As String = "D8"
Dim counter As Single
Set tRange = Range("D23:O42")

For Each r In tRange.Rows
r.Copy Destination:=Range(DestRange)
Range("A9").Offset(0, counter) = Range("P8").Value
counter = counter + 1
Next

counter = 0
For Each c In Range("A9:T9")
If c.Value > testval Then
testval = c.Value
ColOff = counter
End If
counter = counter + 1
Next
Range("D23:O23").Offset(ColOff - 1, 0).Copy Destination:=Range(DestRange)
End Sub

Regards,

Per

 

[shriil]

"shriil" <[email protected]> skrev i en meddelelse












Hi

Try this.

Sub test()
Dim tRange As Range
Dim r, c
Dim testval As Long
Dim ColOff As Long
Const DestRange As String = "D8"
Dim counter As Single
Set tRange = Range("D23:O42")

For Each r In tRange.Rows
    r.Copy Destination:=Range(DestRange)
    Range("A9").Offset(0, counter) = Range("P8").Value
    counter = counter + 1
Next

counter = 0
For Each c In Range("A9:T9")
    If c.Value > testval Then
        testval = c.Value
        ColOff = counter
    End If
    counter = counter + 1
Next
Range("D23:O23").Offset(ColOff - 1, 0).Copy Destination:=Range(DestRange)
End Sub

Regards,

Per- Hide quoted text -

- Show quoted text -

Hi Per

Thanks a Ton. Your code worked perfectly ok. only the last bit,
i.e. ....

For Each c In Range("A9:T9")

If c.Value > testval Then
testval = c.Value
ColOff = counter
End If
counter = counter + 1
Next
Range("D23:O23").Offset(ColOff - 1, 0).Copy Destination:=Range(DestRange)

..... i guess didnt work. Because finally depending upon the highest
value in A9:T9, the particular Row in the Range (D23:O42) gets copied
to Destination Range "D8". Am i right in understanding your code?..
But the final copying did not take place.

Could you please explain the above part of the code?

What is the initial value of 'testval'. Is that the highest value in
the Range A9:T9?

Thanks again for putting me on track

 

[Per Jessen]

Hi Per

Thanks a Ton. Your code worked perfectly ok. only the last bit,
i.e. ....

For Each c In Range("A9:T9")

..... i guess didnt work. Because finally depending upon the highest
value in A9:T9, the particular Row in the Range (D23:O42) gets copied
to Destination Range "D8". Am i right in understanding your code?..
But the final copying did not take place.

Could you please explain the above part of the code?

What is the initial value of 'testval'. Is that the highest value in
the Range A9:T9?

Thanks again for putting me on track

Hi again

Thanks for your reply.

The initial value of 'testval' is 0. After the loop it should be equal to
the highest value it the target range.

'ColOff' is the number of rows to offset. I.e pointing to the row to be
copied.

My mistake. Change the last line to this, and it should work for you

Range("D23:O23").Offset(ColOff, 0).Copy Destination:=Range(DestRange)

Regards,

Per

 

[shriil]

Hi again

Thanks for your reply.

The initial value of 'testval' is 0. After the loop it should be equal to
the highest value it the target range.

'ColOff' is the number of rows to offset. I.e pointing to the row to be
copied.

My mistake. Change the last line to this, and it should work for you

Range("D23:O23").Offset(ColOff, 0).Copy Destination:=Range(DestRange)

Regards,

Per- Hide quoted text -

- Show quoted text -

Hi Per

Got it. It works perfectly now. Thanks again