Excel VBA: copy method of range class failed

Question

This is the code:

Dim rngReason As Range, rngDest As Variant

Set rngDest = wksRemoved.Range("A1").CurrentRegion
Set rngDest = rngDest.Cells(rngDest.Rows.Count + 1, 1)

Set rngReason = Range("A1").CurrentRegion
Set rngReason = rngReason.Columns(rngReason.Columns.Count).offset(0, 1)
Set rngReason = Intersect(rngReason, rngDupeRows)

rngReason.Cells.Value2 = "Duplicate " & strDedupeField

''Fails here with the error message:
''  Copy method of range class failed
rngDupeRows.Copy Destination:=rngDest

rngDupeRows is a range. The values of rngDupeRows and rngDest addresses at the time of the error are:

rngDest.Address      "$A$2"       Variant/String
rngDupeRows.Address  "$3:$98301"  String

The purpose of the code is to copy rows from one workbook and paste them into another. I've ran this multiple times on different ranges and it works fine.

Any ideas what the reason for the error might be?

@martin failing to declare that variable should raise 424 "Object Required" error. Neglecting to assign it should raise 91 "Object variable or with block not set". — David Zemens, Oct 08 '14 at 14:35
You are copying the entire rows from 3 to 98301, Excel might be having a problem with the size of this range. Can you change the address of rngDupeRows to specific columns? — Dave, Oct 08 '14 at 14:41
rngDupeRows is declared somewhere else, this is not the problem. — jivko, Oct 08 '14 at 14:46
Dave, if excel was having problems with resources, I wouldn't expect the error to be 'copy method failed'. I've done the copy/paste manually of all these rows, and there wasn't a problem. — jivko, Oct 08 '14 at 14:49
Just a thought; is the other workbook in compatibility mode and restricted to 65K-odd rows? — , Oct 08 '14 at 14:50
Could also be a problem with workbook/worksheet *protection*. — David Zemens, Oct 08 '14 at 14:52
IDK, I don't set this explicitly, it should have the same settings as any new workbook created... — jivko, Oct 08 '14 at 14:58
Please note that the method works with other ranges. For example when the source range is just a few rows. — jivko, Oct 08 '14 at 14:59
Okay, the error disappeared when I made the source range to include not the entire rows but up to a point where there's data. — jivko, Oct 08 '14 at 16:45

score 0 · Answer 1 · answered Oct 08 '14 at 18:54

Might take a long time, but if it must be done then instead of using "copy" you could try adding a loop to assign a value for each cell in the range

        Dim rngReason As Range, rngDest As Variant
        Set rngDest = wksRemoved.Range("A1").CurrentRegion
        Set rngDest = rngDest.Cells(rngDest.Rows.Count + 1, 1)

        Set rngReason = Range("A1").CurrentRegion
        Set rngReason = rngReason.Columns(rngReason.Columns.Count).offset(0, 1)
        Set rngReason = Intersect(rngReason, rngDupeRows)

        rngReason.Cells.Value2 = "Duplicate " & strDedupeField

       'rngDupeRows.Copy Destination:=rngDest
        ' Try messing around with something like this code.
        Dim rngTmp As Range
        For Each rngTmp In rngDest
            rngTmp.value = rngDupeRows.Value
        Next
        '***

Excel VBA: copy method of range class failed

1 Answers1