将活动工作表复制到另一个工作簿:范围类的复制方法失败

我希望我的代码复制整个工作表(SOURCE)并将其粘贴到其他工作簿(WHERE_I_WANNA_PASTE_IT)下的其他工作表(TARGET)中并保存.

我收到此错误:

运行= -time错误'1004':范围类的复制方法失败

在这条线上:

CurrentSheet.Cells.Copy Destination:=oSheet.cells 

代码:

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

Matteo NNZ.. 6

一些建议,这应该工作(至少它在我的电脑上,我能够完美复制你的错误):

修正1

不要创建新的Excel应用程序,使用相同的线程; 换句话说,删除这个:

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

写下这个:

Set oBook = Workbooks.Open(path1)

修正2

您可以根据需要设置活动工作表,但要明确参考,请使用"ThisWorkbook",这样编译器会很高兴,您也没有风险参考其他工作表(这始终是一个很好的做法,从不完全信任默认引用,如果您已经知道您的预期引用是什么,例如在这种情况下):

Set CurrentSheet = ThisWorkbook.ActiveSheet

和建议......

放错误处理程序:如果方法失败,你会发现有一堆openend Excel线程(检查出来,你将拥有尽可能多的运行你的代码.这是我如何编写完整的代码(建议)包括):

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

注意

打开你的任务管理器(Ctrl + Alt + Del)并转到进程...关闭你每次运行代码失败时可能已经离开的所有EXCEL.EXE进程,在你的笔记本电脑爆炸之前:)

一些建议,这应该工作(至少它在我的电脑上,我能够完美复制你的错误):

修正1

不要创建新的Excel应用程序,使用相同的线程; 换句话说,删除这个:

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

写下这个:

Set oBook = Workbooks.Open(path1)

修正2

您可以根据需要设置活动工作表,但要明确参考,请使用"ThisWorkbook",这样编译器会很高兴,您也没有风险参考其他工作表(这始终是一个很好的做法,从不完全信任默认引用,如果您已经知道您的预期引用是什么,例如在这种情况下):

Set CurrentSheet = ThisWorkbook.ActiveSheet

和建议......

放错误处理程序:如果方法失败,你会发现有一堆openend Excel线程(检查出来,你将拥有尽可能多的运行你的代码.这是我如何编写完整的代码(建议)包括):

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

注意

打开你的任务管理器(Ctrl + Alt + Del)并转到进程...关闭你每次运行代码失败时可能已经离开的所有EXCEL.EXE进程,在你的笔记本电脑爆炸之前:)