copying active sheet into another workbook: Copy Method of Range class failed

Question

I want my code to copy the entire worksheet(SOURCE) and paste it into some other worksheet(TARGET) under other workbook(WHERE_I_WANNA_PASTE_IT) and save it.

I am getting this error:

Run=-time error '1004': Copy Method of Range class failed

on this line:

CurrentSheet.Cells.Copy Destination:=oSheet.cells

The code:

Public Const path1 As String = "C:\Where_I_WANNA_PASTE_IT.xlsb"
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim CurrentSheet As Object

Sub copyNpaste

Set CurrentSheet = ActiveSheet
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(path1)
Set oSheet = oBook.Worksheets("TARGET")

'Deleting what's on "TARGET" first
oSheet.cells.delete

'This is where the Error happens.
CurrentSheet.Cells.Copy _
Destination:=oSheet.Cells

oBook.Save
oBook.Close

Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set CurrentSheet = Nothing

End Sub

I am unable to reproduce your error. Is there a reason you are using late binding? — TheEngineer, Jan 13 '15 at 18:54
Try this and see if it works. `CurrentSheet.Cells.CopySpecial` — SaladSnake, Jan 13 '15 at 18:54

Matteo NNZ · Accepted Answer · 2015-01-13T19:02:34.730

A couple of suggestions, this should work (at least it does on my computer, I was able to perfectly replicate your bug):

FIX 1

Don't create a new Excel application, use the same thread; in other words, remove this:

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(path1)

and write this:

Set oBook = Workbooks.Open(path1)

FIX 2

You can set your active sheet when you want, but be clear with the reference, use "ThisWorkbook" so the compiler will be happy and you don't risk to reference the other sheet (this is always a good practice, never fully trust the default reference if you already know what your expected reference is, like in this case):

Set CurrentSheet = ThisWorkbook.ActiveSheet

and a SUGGESTION...

Put an error handler: if the method fails, you will find with a bunch of openend Excel threads (check it out, you will have as many as you have already failed running your code. Here is how I would write the full code (suggestion included):

Public Const path1 As String = "C:\yourfile.xlsb"
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim CurrentSheet As Object

Sub copyNpaste()

Set oBook = Workbooks.Open(path1)
Set oSheet = oBook.Worksheets("TARGET")
Set CurrentSheet = ThisWorkbook.ActiveSheet

On Error GoTo ESCAPE 'if the code fails, we go to "Escape" and at least we close the file

'Deleting what's on "TARGET" first
oSheet.Cells.Delete

'This is where the Error happens.
CurrentSheet.Cells.Copy _
Destination:=oSheet.Cells

oBook.Save

ESCAPE:

oBook.Close

Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set CurrentSheet = Nothing

End Sub

NOTE

Open your Task Manager (Ctrl+Alt+Del) and go to processes... close all the EXCEL.EXE processes you have probably left everytime you have failed running your code, before your laptop explodes :)

copying active sheet into another workbook: Copy Method of Range class failed

1 Answers1