将活动工作表复制到另一个工作簿：Range类的复制方法失败

J Lee 发表于 Dev

李杰

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

我收到此错误：

运行=时间错误“ 1004”：Range类的复制方法失败

在这条线上：

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

新西兰国家博物馆

有两个建议，这应该可以工作（至少在我的计算机上可以，我能够完美地复制您的错误）：

修复1

不要创建新的Excel应用程序，请使用相同的线程；换句话说，删除此：

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

并写成这样：

Set oBook = Workbooks.Open(path1)

修复2

您可以根据需要设置活动工作表，但要清楚参考，请使用“ ThisWorkbook”，这样编译器将很高兴，并且您不会冒险参考其他工作表（这始终是一个好习惯，永远不要完全信任如果您已经知道您的期望参考是默认参考，例如本例）：

Set CurrentSheet = ThisWorkbook.ActiveSheet

和建议...

放置一个错误处理程序：如果该方法失败，您将发现一堆开放式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