vba 粘贴特殊错误 1004 Range 类的 PasteSpecial 方法失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45195868/
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
Paste Special error 1004 PasteSpecial method of Range class failed
提问by Justtheintern
I have looked thoroughly at the current answers for this problem and none of them have fixed mine.
我已经彻底查看了这个问题的当前答案，但没有一个解决了我的问题。
The operation is simply copying a selection of a sheet and copying to a new book called budget.
该操作只是简单地复制一张纸的选择并复制到一本名为“预算”的新书上。
Again I have tried multiple different ways of doing the same thing and none of them seem to change this error. The select method works,it only breaks when I try to paste.
我再次尝试了多种不同的方法来做同样的事情，但似乎没有一个能改变这个错误。select 方法有效，只有当我尝试粘贴时它才会中断。
Code:
代码：
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs FileName:=ThisWorkbook.path & "\" & "Budget.xlsx"
wk.Activate
wk.Unprotect
wk.Worksheets("Sheet1").Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Thanks,
谢谢，
采纳答案by Shai Rado
Try the code below (explanation inside the code as comments):
试试下面的代码（代码中的解释作为注释）：
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs Filename:=ThisWorkbook.Path & "\" & "Budget.xlsx"
wk.Activate
wk.Unprotect
' have the Copy>>Paste section together
Dim LastCol As Long
Dim LastRow As Long
' you never mentioned which sheet to copy from, I used the first index
With ThisWorkbook.Sheets(1)
LastCol = .Range("B3").End(xlToRight).Column
LastRow = .Range("B3").End(xlDown).Row
.Range("B3", .Cells(LastRow, LastCol)).Copy ' <-- Copy without Select
End With
' Paste without Select
wk.Worksheets("Sheet1").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
回答by aucuparia
If you try exactly the steps in the code manually, you will find it also fails. The issue is that inbetween copying the cells and trying to paste them, you are creating a new workbook and saving it. This cancels copy/paste mode (i.e. the "marching ants" around the copied range disappear), so there is nothing to paste.
如果您完全手动尝试代码中的步骤，您会发现它也失败了。问题是在复制单元格和尝试粘贴它们之间，您正在创建一个新工作簿并保存它。这会取消复制/粘贴模式（即复制范围周围的“行军蚂蚁”消失），因此没有任何可粘贴的内容。
The solution is to not use Selectionat all. In general any time you find yourself writing .Selectin VBA you're doing it wrong (see this questionfor detail). Here is how I would re-write your code:
解决方案是根本不使用Selection。一般来说，任何时候你发现自己.Select用 VBA编写你都做错了（有关详细信息，请参阅此问题）。以下是我将如何重写您的代码：
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs Filename:=ThisWorkbook.Path & "\" & "Budget.xlsx"
ThisWorkbook.Range("B3").CurrentRegion.Copy
wk.Worksheets("Sheet1").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
