vba 将活动工作表复制到另一个工作簿中：Range 类的复制方法失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27928293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
copying active sheet into another workbook: Copy Method of Range class failed
提问by J Lee
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.
我希望我的代码复制整个工作表（SOURCE）并将其粘贴到其他工作簿（WHERE_I_WANNA_PASTE_IT）下的其他工作表（TARGET）中并保存。
I am getting this error:
我收到此错误：
Run=-time error '1004': Copy Method of Range class failed
Run=-time error '1004'：Range 类的复制方法失败
on this line:
在这一行：
CurrentSheet.Cells.Copy Destination:=oSheet.cells
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
回答by Matteo NNZ
A couple of suggestions, this should work (at least it does on my computer, I was able to perfectly replicate your bug):
一些建议，这应该可行（至少在我的计算机上可行，我能够完美地复制您的错误）：
FIX 1
修复 1
Don't create a new Excel application, use the same thread; in other words, remove this:
不要创建新的 Excel 应用程序，使用同一个线程；换句话说，删除这个：
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(path1)
and write this:
并写下这个：
Set oBook = Workbooks.Open(path1)
FIX 2
修复 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):
您可以在需要时设置活动工作表，但要清楚参考，使用“ThisWorkbook”，这样编译器就会很高兴，并且您不会冒险引用其他工作表（这始终是一个好习惯，永远不要完全相信默认参考（如果您已经知道预期的参考是什么，例如在这种情况下）：
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):
放置一个错误处理程序：如果该方法失败，您会发现有一堆 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
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 :)
打开您的任务管理器 (Ctrl+Alt+Del) 并转到进程...关闭所有 EXCEL.EXE 进程，每次运行代码失败时，在您的笔记本电脑爆炸之前，您可能会离开:)
