Is it possible to avoid using the clipboard by copying a range?-CodePudding

for some reason, this simple code that copies one range from one worksheet to another takes far too long to complete. Is it possible that this is due to the use of a clipboard? Any help would be appreciated.


Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False


With Sheets("Sheet 1")
    .range(.range("B4"), .range("Z4").End(xlDown)).copy
End With

Sheets("test").range("B7").PasteSpecial xlPasteValues

Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

CodePudding user response：

Create a rng object then use Resize.

dim rng as Range
With Sheets("Sheet 1")
    Set rng = .range(.range("B4"), .range("Z4").End(xlDown))
End With

Sheets("test").range("B7").Resize(rng.rows.count,rng.Columns.Count).Value = rng.Value

Note I only used the range object to facilitate the Resize. Another method would be another with block:


With Sheets("Sheet 1")
    with.range(.range("B4"), .range("Z4").End(xlDown))
        Sheets("test").range("B7").Resize(.rows.count,.Columns.Count).Value = .Value
    End With
End With

CodePudding user response：

Using the Destination argument of .Copy actually skips loading the data into the clipboard and keeps everything in the Excel app. This should be much faster and copies over all the contents, formulas and formatting of the cells.

With Sheets("Sheet 1")
   .Range(.Range("B4"), .Range("Z4").End(xlDown)).Copy Destination:=Sheets("test").Range("B7")
End With