Excel copy(Destination:=) failed in VB.NET

Question

I'm using copy(Destination:=) and would like to copy whole records in one excel to another excel file but failed to execute. Could it because of need to use range? Because there is not necessary to use range as i would like to copy all and paste into another excel file

oWorkBook.Sheets(1).Cells.Copy(Destination:=oWorkBookOri.Sheets(1).Range("A" & oWorkBookOri.Sheets(1).UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row + 1))

It mentioned error as below.

"Copy method of Range class failed".

My Previous code are as below

oWorkBook.Sheets(1).Cells.Copy()
oWorkBookOri.Sheets(1).Range("A" & oWorkBookOri.Sheets(1).UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row + 1).Select()
oWorkBookOri.Sheets(1).Paste

and it keep occur error message "System.Runtime.InteropServices.COMException (0x800A03EC): Paste method of Worksheet class failed" and despite on this, i changed to copy destination syntax and occur error as mentioned above.

Can you explain what you mean be whole records. If you copy the whole workbook with .Cells.Copy() you have to paste it to cell(A1) on the target sheet Maybe you want .UsedRangeCopy. — CDP1802
Whole records means all records in that sheet, could you assists to show me where to change the code? — Wai Lun
Yes but all the records means all 1,0485,576 rows which if you paste into the target destination sheet will overwrite any existing. If you want to do that use Destination:=oWorkBookOri.Sheets(1).Range("A1") — CDP1802
Oh, but destination will be different cells, thats why i define the range in destination, could you assists me? Should i change .cells.copy to another? — Wai Lun
If you use .UsedRangeCopy instead of .Cells.Copy you should get all the records that have values. Then you can paste in the Destination you have. The problem might be if the used range starts in a column other than A which is where your destination is. Would that be a problem ? — CDP1802

CDP1802 CDP1802 · Accepted Answer · 2020-03-06T10:08:00

If you use .Cells.Copy() which is every cell on the sheet, the destination has to be Range("A1") otherwise there is not enough room for the copied cells. I assume you want to only copy used rows in which case try

Dim rngSource As Range, rngTarget As Range, targetRow As Long

rngSource = oWorkbook.Sheets(1).UsedRange
With oWorkbookOri.Sheets(1)
    targetRow = .UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row + 1
    rngTarget = .cells(targetRow, rngSource.Column)
End With

rngSource.Copy(rngTarget)
MsgBox("Copied " & rngSource.Address & " to " & rngTarget.Address, vbInformation)

Excel copy(Destination:=) failed in VB.NET

2 Answers