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

Question

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

Scott Craner · Accepted Answer · 2021-12-10T15:04:32.850

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

score 2 · Answer 2 · answered Dec 10 '21 at 15:09

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

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

2 Answers2