我怎样才能加快这个过程，因为复制和粘贴需要很长时间？

Is there anyway to combine to following steps since I am pulling the data from the same sheet into another sheet and the process is very slow.

无论如何都要结合以下步骤,因为我将数据从同一张纸拉到另一张纸上,而且过程非常慢。

Sheets("CDGL").Select
Sheets("CDGL").Range("H2:J" & Cells(Rows.Count, "G").End(xlUp).Row).Copy Destination:=Sheets("Rec").Range("B6")

Sheets("CDGL").Select
Sheets("CDGL").Range("L2:O" & Cells(Rows.Count, "K").End(xlUp).Row).Copy Destination:=Sheets("Rec").Range("E6")

Sheets("CDGL").Select
Sheets("CDGL").Range("AJ2:AJ" & Cells(Rows.Count, "AI").End(xlUp).Row).Copy Destination:=Sheets("Rec").Range("I6")

3 个解决方案

#1

You can simply assign the values directly using the Resize() Function to make the ranges the same size:

您可以使用Resize()函数直接分配值,以使范围大小相同:

With Sheets("CDGL")
    Sheets("Rec").Range("B6").Resize(.Cells(.Rows.Count, "G").End(xlUp).Row - 1, 3).Value = .Range("H2:J" & .Cells(.Rows.Count, "G").End(xlUp).Row).Value
    Sheets("Rec").Range("E6").Resize(.Cells(.Rows.Count, "K").End(xlUp).Row - 1, 4).Value = .Range("L2:O" & .Cells(.Rows.Count, "K").End(xlUp).Row).Value
    Sheets("Rec").Range("I6").Resize(.Cells(.Rows.Count, "AI").End(xlUp).Row - 1, 1).Value = .Range("AJ2:AJ" & .Cells(.Rows.Count, "AI").End(xlUp).Row).Value
End With

I also used a With Block and the Appropriate . identifier to reduce typing and removing the continual selection of sheets. This will also cut down the time.

我还使用了With Block和适当的。标识符,以减少键入和删除连续选择的工作表。这也将减少时间。

#2

When you need a fast copy/paste, avoid copying to clipboard and pasting again. You could have simply used the Range.Value method, something like the following:

当您需要快速复制/粘贴时,请避免复制到剪贴板并再次粘贴。您可以简单地使用Range.Value方法,如下所示:

With Sheets("CDGL")
rows_c1 = .Cells(Rows.Count, "G").End(xlUp).Row
Sheets("Rec").Range("B6:D" & rows_c1).Value = Sheets("CDGL").Range("H2:J" & rows_c1).Value

rows_c2 = .Cells(Rows.Count, "K").End(xlUp).Row
Sheets("Rec").Range("E6:H" & rows_c2).Value = Sheets("CDGL").Range("L2:O" & rows_c2).Value

rows_c3 = .Cells(Rows.Count, "AI").End(xlUp).Row
Sheets("Rec").Range("I6:I" & rows_c3).Value = Sheets("CDGL").Range("AJ2:AJ" & rows_c3).Value
End With

Should you need to have the formatting of cells copied, the Range property "Value" can have 3 optional arguments 10,11,12. 11 is what you need to transfer both value, formats, and formulas. Something like this:

如果需要复制单元格的格式,Range属性“Value”可以有3个可选参数10,11,12。 11是您需要传输值,格式和公式。像这样的东西:

Sheets("Rec").Range("B6:D" & rows_c1).Value(11) = Sheets("CDGL").Range("H2:J" & rows_c1).Value(11)

Also, avoid using "Select" and use the "With....End With" statement to speed up the execution.

另外,避免使用“选择”并使用“With .... End With”语句来加速执行。

#3

If your issue isn't the effectiveness of the code but the time it takes to work, add Application.ScreenUpdating = False to the start of your sub and Application.ScreenUpdating = True to the end. This will speed up the wait time immensely

如果您的问题不是代码的有效性,而是工作所需的时间,请将Application.ScreenUpdating = False添加到sub的开头,将Application.ScreenUpdating = True添加到结尾。这将极大地加快等待时间

#1