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 

• JvdV: 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?
• Benjamin Huis: @JvdV yes you're right, changed it!

Answer 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

• Benjamin Huis: 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: @BenjaminHuis, see updated answer. I hope it is what you try to achieve.