将粘贴VBA范围从一个工作表复制到另一个工作表中循环并转置数据 [英] Copy Paste VBA Range from one to another worksheet Loop and transpose the data

问题描述

对于Excel中的模型，我想将数据从工作簿中的一张工作表复制并粘贴到工作簿中的另一张工作表，并使用for next循环使用此数据.来自工作文件CME的数据应粘贴到RME的工作文件中.数据的总行数为2420.但是，由于复制和粘贴不同工作簿中的范围不同并且步长也不同，因此我假设我应该使用两个不同的变量i和j.前两行代码是无需循环的手动方式

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.

来自文件的示例:在Excel Worksheet CME中:复制范围J2:J10以将其传输到工作表RME上，以粘贴到B2:J2上，因此在其中具有转置功能很重要.对于下一个范围，我需要复制范围J12:J20以将其传输到工作表RME，以粘贴到B3:J3上，依此类推，因此我需要创建一个循环吗?对于复制和粘贴范围，有两个不同的步骤大小.非常感谢！

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!

我想出了以下公式:

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   

推荐答案

我对您真正想做的事情有些困惑，但这会做吗?

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

第一个代码:不使用转置

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

第二个代码:使用转置

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:

• 第一个代码: 0.13秒
• 第二个代码: 5,5秒

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

这篇关于将粘贴VBA范围从一个工作表复制到另一个工作表中循环并转置数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！