vba VBA粘贴法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14756438/
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
VBA paste method
提问by caro
I've been working on learning ways to better this code and taking examples from this site and others -- however I can't seem to get past the runtime error 1004 "paste method of worksheet class failed". I have two other similar macros and a button which will run all 3. It runs the first two with this same syntax around pasting into the "MyQueue" file just fine, but the third it will not paste and throws this error. Can anyone help?
我一直在努力学习改进此代码的方法，并从该站点和其他站点中获取示例 - 但是我似乎无法克服运行时错误 1004“工作表类的粘贴方法失败”。我还有另外两个类似的宏和一个将运行所有 3 个的按钮。它运行前两个使用相同的语法围绕粘贴到“MyQueue”文件就好了，但第三个它不会粘贴并引发此错误。任何人都可以帮忙吗？
Sub CSQAgentSummaryEdit()
Dim MyPath As String
MyPath = " path "
MyFile = " file "
QueuePath = "path "
MyQueue = " file "
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = Workbooks.Open(QueuePath)
Set wb2 = Workbooks.Open(MyPath)
Columns("A:V").Delete Shift:=xlUp
Columns("B").Delete Shift:=xlUp
Columns("C:R").Delete Shift:=xlUp
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.consolidate Sources:= _
"'file data " _
, Function:=xlSum, LeftColumn:=True
Range("A1").CurrentRegion.Delete Shift:=xlUp
Rows("1:1").Delete
Range("A1").CurrentRegion.Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Copy
Workbooks.Open (QueuePath)
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(20, 0).Range("A1").Select
ActiveSheet.Paste , False
Workbooks(MyQueue).Save
Workbooks(MyFile).Close False
End Sub
回答by evoandy
You seem to be opening the QueuePath workbook twice.
您似乎要打开 QueuePath 工作簿两次。
Instead of
代替
Selection.Copy
Workbooks.Open (QueuePath)
Range("A1").Select
Try
尝试
Selection.Copy
wb2.Sheets("Sheet1").Range("A1").Select
Specifying which workbook you are using with will help avoid pasting to the wrong workbook/pasting errors.
指定您正在使用的工作簿将有助于避免粘贴到错误的工作簿/粘贴错误。
回答by baldmosher
Remember that Excel is affected by external influences and by its own behaviour. VBA generally isn't. Active-anything is generally undesirable. Always better to:
请记住，Excel 会受到外部影响和自身行为的影响。VBA 通常不是。主动-任何东西通常都是不可取的。总是更好：
set wsc = Sheets("Copy Sheet")
set wsp = Sheets("Paste Sheet")
..and then specify ranges within.
..然后指定范围内。
I also always use .PasteSpecial instead of .Paste -- it causes far fewer errors and is not reliant on activating the sheet, but that's actually the clue to your problem.
我也总是使用 .PasteSpecial 而不是 .Paste - 它会导致更少的错误并且不依赖于激活工作表，但这实际上是您问题的线索。
So, this part:
所以，这部分：
Selection.Copy
Workbooks.Open() 'I also think you did this twice so it could be removed, but see below
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(20, 0).Range("A1").Select
ActiveSheet.Paste , False
You don't need to select each cell, and as a minor point, .Cells(1) can be easier to read than .Range("A1") when specifying a single cell -- but both works. Just go for broke, and try this instead:
您不需要选择每个单元格，作为一个小点，在指定单个单元格时， .Cells(1) 比 .Range("A1") 更容易阅读 - 但两者都有效。就破产了，试试这个：
set rngToCopy = Selection
Set wsToPaste = wb2.Sheets("Name of Sheet") 'you don't need this if there's one sheet in the wb
rngToCopy.copy
wsToPaste.Cells(1).End(xlDown).Offset(20, 0).PasteSpecial
Application.CutCopyMode = False
回答by Smandoli
Possibly the workbook you need to be active is not the right workbook. Check this link: Excel VBA pastelink run-time error 1004: Application-defined or object-defined error
可能您需要激活的工作簿不是正确的工作簿。检查此链接：Excel VBA 粘贴链接运行时错误 1004：应用程序定义或对象定义错误
