Copy Paste VBA Range from one to another worksheet Loop and transpose the data

Question

For A model in Excel I would like to copy and paste data from one sheet in a workbook to the other sheet in the workbook and transpose this data with the usage of a for next loop. Data from the workfile CME should be pasted in the workfile of RME. The total lines of data is 2420. However, since the ranges in the copy and paste different workbook are different and step sizes are different, I assume that i should use two different variables i and j. The first two lines of code is the manual way to do it without a loop

When I try to run this code, it will give me an error. the first to codes are an example how it should be done manually without a loop. Thereafter the loop starts. Already a lot of thanks for the person who can help me with this issue.

Example from the file: In Excel Worksheet CME: copy range J2:J10 to transfer it to worksheet RME to be pasted on B2:J2 so it is important to have a transpose function in it. For the next range I need to copy the range J12:J20 to transfer it to worksheet RME to be pasted on B3:J3 and so on, therefore I need to create a loop right? With two different stepsizes for the copy and paste ranges. Thanks a lot already!

I came up with the following formula:

Dim i As Long
Dim j As Long

Worksheets("CME").Range("J2:J10").Copy
Worksheets("RME").Range("B2").PasteSpecial Transpose:=True

Worksheets("CME").Range("J12:J20").Copy
Worksheets("RME").Range("B3").PasteSpecial Transpose:=True

For i = 11 To 2420 Step 10
    Worksheets("CME").Range (Cells(i + 11, 10)), Cells(i + 19, 10).Copy

    For j = 3 To 2420 Step 1
    Worksheets("RME").Range(Cells(j + 1, 2)).PasteSpecial Transpose:=True
    Next j

Next i

End Sub

In your titel you want to copy from another workbook, but in your code and explanation you seem to copy between sheets....which is it? — JvdV
– JvdV, Commented Aug 2, 2018 at 8:48

JvdV · Accepted Answer · 2018-08-02 09:15:47Z

1

I'm a little confused with what exactly you want to do but would this do?:

First code: Not using transpose

Sub Test()

Dim X As Long, Y As Long, Z As Long

Z = 2
For X = 2 To 2420 Step 10
    For Y = 2 To 10
        Sheets("RME").Cells(Z, Y).Value = Sheets("CME").Cells(X + (Y - 1), 10).Value
    Next Y
    Z = Z + 1
Next X

End Sub

Second code: Using transpose

Sub Test()

Dim X As Long, Z As Long
Dim RNG1 As Range, RNG2 As Range

Sheets("CME").Activate
Z = 2
For X = 2 To 2420 Step 10
    Set RNG1 = Sheets("CME").Range(Cells(X, 10), Cells(X + 9, 10))
    Set RNG2 = Sheets("RME").Cells(Z, 2)
    RNG1.Copy
    RNG2.PasteSpecial Transpose:=True
    Z = Z + 1
Next X
Sheets("RME").Activate

End Sub

Note: Copy/Pasting and transpose take much longer to execute on a larger database. To compare the two code examples above:

First code: 0,13 seconds
Second code: 5,5 seconds

edited Aug 2, 2018 at 9:15

answered Aug 2, 2018 at 8:53

JvdV

76.8k8 gold badges47 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benjamin Huis Over a year ago

Hi JvdV, the following are the first two lines in a way to do it manual, however for 2420 rows to do it manual it is pretty a time waste. That is why I would like to create a loop to do this Worksheets("CME").Range("J2:J10").Copy Worksheets("RME").Range("B2").PasteSpecial Transpose:=True Worksheets("CME").Range("J12:J20").Copy Worksheets("RME").Range("B3").PasteSpecial Transpose:=True

JvdV Over a year ago

@BenjaminHuis, see updated answer. I hope it is what you try to achieve.

Collectives™ on Stack Overflow

Copy Paste VBA Range from one to another worksheet Loop and transpose the data

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related