Avoiding the clipboard when copying large ranges to another workbook?

Question

I'm interested in copying a large range from one workbook to another. Currently, I am using the copy and paste functions. The macro worked on smaller files, but now I have a very large file and I am running into an error at ActiveSheet.Paste. I think it is because what I am copying is too large for the clipboard. Does that sound right? If so, I would like to avoid the clipboard all together. Here is my code currently.

DeptReceivedWB.Sheets(1).Cells(1, 1).CurrentRegion.Copy


APPS_AuditWB.Activate
APPS_AuditWB.Sheets(3).Select
ActiveSheet.Cells(i + 1, 1).Select
ActiveSheet.Paste
ActiveSheet.Cells(i + 1, 1).EntireRow.Delete

If you don't need formatting, just set one range to another. Avoid copy/paste all together. — findwindow, Sep 25 '15 at 22:32

paul bica · Accepted Answer · 2015-09-26T12:31:14.310

1

Use one of these:

Option Explicit

Public Sub valuesCopy()
    With DeptReceivedWB.Sheets(1).Cells(1, 1).CurrentRegion
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).Copy
    End With

    APPS_AuditWB.Sheets(3).Cells(1, 1).PasteSpecial xlPasteValues
End Sub

Public Sub directCopy1()
    Dim cr As Range, fr As Long, lr As Long, fc As Long, lc As Long

    Set cr = DeptReceivedWB.Sheets(1).Cells(1, 1).CurrentRegion
    fr = cr.Row:    lr = fr + cr.Rows.Count - 2
    fc = cr.Column: lc = fc + cr.Columns.Count - 1
    Set cr = cr.Offset(1).Resize(lr, lc)

    With APPS_AuditWB.Sheets(3)
        .Range(.Cells(fr, fc), .Cells(lr, lc)).Value2 = cr.Value2
    End With
End Sub

Public Sub directCopy2()
    Dim cr As Range, fr As Long, lr As Long, fc As Long, lc As Long

    Set cr = DeptReceivedWB.Sheets(1).Cells(1, 1).CurrentRegion
    fr = cr.Row:    lr = cr.Rows.Count - 1
    fc = cr.Column: lc = cr.Columns.Count
    Set cr = cr.Offset(1).Resize(lr, lc)

    With APPS_AuditWB.Sheets(3)
        .Cells(fr, fc).Resize(lr, lc).Value2 = cr.Value2
    End With
End Sub

edited Sep 26 '15 at 12:31

answered Sep 25 '15 at 22:51

paul bica

10,557
4
23
42

1

Typo - `lc` isn't assigned a value. But this only works if the copied range begins in A1 - maybe better to use `.Cells(fr, fc).Resize(lr, lc).Value2= ur.Value2` ? – Tim Williams Sep 26 '15 at 06:40
Thank you @paul bica, I used directCopy1 and it worked like a charm! This is my first time seeing Value2. I take it that Value2 is for the value of a range, where plain old value is just for a single cell? – Bryan F Sep 28 '15 at 14:00
I'm glad it helped. Value2 is similar to "raw data" where no formatting is involved. The [difference between .Value and .Value2](http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) is related to formatting of the Currency and Date data types. .Value2 is the faster and more reliable method because it returns the date as a number (serial), for example if A1 contains **NOW()**: .Text: **#######**, .Value: **9/28/2015 6:43:43 PM**, .Value2: **42275.7803555556**, .Formula: **=NOW()** – paul bica Sep 28 '15 at 22:50

score 0 · Answer 2 · answered Sep 26 '15 at 08:03

I would suggest that you only copy the range you want rather than copying a (presumably) header row and then deleting it from the target:

Dim rgCopy as range
set rgCopy = DeptReceivedWB.Sheets(1).Cells(1, 1).CurrentRegion
set rgCopy = rgCopy.Resize(rgCopy.Rows.Count - 1).Offset(1)
rgCopy.Copy Destination:=APPS_AuditWB.Sheets(3).Cells(i + 1, 1)

Avoiding the clipboard when copying large ranges to another workbook?

2 Answers2