vba Range.Paste - 对象不支持此属性或方法

Range.Paste - Object doesn't support this property or method

提问by aLearningLady

I've got a very simple procedure that copies a range from one workbook and pastes it into another; the issue is, I'm getting the error in the title on the pasteline.

我有一个非常简单的过程，可以从一个工作簿中复制一个范围并将其粘贴到另一个工作簿中；问题是，我在标题中遇到错误paste。

Below is the code:

下面是代码：

Sub copypasta()
Dim x As Workbook
Dim y As Workbook

Set x = ActiveWorkbook
Set y = Workbooks.Open("F:\Target\FTB\FTB.xlsx")

x.Sheets(1).Range("A1").CurrentRegion.Copy

y.Sheets("DTR").Cells.Delete '<~~~No Error Here...?
y.Sheets("DTR").[a1].Paste   '<~~~Error Here

I thought it was strange that the deletion works fine, but paste does not.

我认为删除工作正常很奇怪，但粘贴却没有。

It seems that I may be missing some fundamentals of the paste method - could someone please explain what I'm missing here?

似乎我可能遗漏了粘贴方法的一些基础知识 - 有人可以解释一下我在这里遗漏了什么吗？

回答by Mathieu Guindon

Other answers have provided ways to make it work, but don't explain what's going on.

其他答案提供了使其工作的方法，但没有解释发生了什么。

y.Sheets("DTR")

That should really be y.Worksheets("DTR"), because the Sheetscollection can contain non-worksheet items, such as charts for example.

那真的应该是y.Worksheets("DTR")，因为Sheets集合可以包含非工作表项目，例如图表。

Regardless, both the Sheetsand the Worksheetscollections' Itemproperty (which is the default propertyof any collection type) yields an Object, which makes every chained member call that follows, a late-bound call.

无论如何，两个Sheets和Worksheets集合Item属性（它是默认属性的任何集合类型的）产生一个Object，这使得后面的每个链式部件呼叫，后期绑定呼叫。

And you don't get IntelliSenseon late-bound calls, since these calls get resolved at run-time, by definition: you can call anythingon an Object, and the code will happily compile:

并且您不会在后期绑定调用中获得IntelliSense，因为这些调用在运行时得到解决，根据定义：您可以在 上调用任何内容Object，并且代码将很容易编译：

Sub Test()
    Debug.Print ThisWorkbook.Worksheets(1).Whatever
End Sub

Same as:

与...一样：

Sub Test()
    Debug.Print ThisWorkbook.Worksheets.Item(1).Whatever
End Sub

At run-time, if VBA can't find the Whatevermember on the retrieved object's interface, it raises run-time error 438, "Object doesn't support this property or method".

在运行时，如果 VBAWhatever在检索到的对象的接口上找不到该成员，则会引发运行时错误 438，“对象不支持此属性或方法”。

Late-binding is powerful and very useful, but it also incurs overhead that you don't necessarily need.

后期绑定功能强大且非常有用，但它也会产生您不一定需要的开销。

Instead of working off an Object, you can castthe returned object reference to a specific type when you know what that type is - in this case we knowwe're dealing with a Worksheetobject:

相反，关闭的工作Object，你可以投返回的对象引用到一个特定的类型，当你知道是什么类型-在这种情况下，我们知道我们正在处理一个Worksheet对象：

Dim target As Worksheet
Set target = y.Worksheets("DTR")

Now that you have an early-boundWorksheetobject reference, IntelliSensecan guide you:

现在您有了一个早期绑定的Worksheet对象引用，IntelliSense可以指导您：

And if you try to call a bogus member (e.g. target.Whatever), you'll get a compile-time error instead of a run-time error.

如果你试图调用一个伪造的成员（例如target.Whatever），你会得到一个编译时错误而不是运行时错误。

When you do this:

当你这样做时：

target.[A1].Paste

You're using late-binding again, to retrieve the A1range. Instead, call the Worksheet.Rangeproperty getter to retrieve an early-bound Rangeobject - and from there you'll see that when you type .pastethere is no such thing as a Pastemethod in a Rangeobject:

您再次使用后期绑定来检索A1范围。相反，调用Worksheet.Range属性 getter 来检索一个早期绑定的Range对象 - 从那里你会看到当你输入时.paste，对象中没有Paste方法这样的东西Range：

And you get autocompletion and tooltips for everything as you type it:

并且您在键入时获得所有内容的自动完成和工具提示：

回答by Shai Rado

You can Copyand PasteRangesbetween sheets (and workbooks) using 1 line of code, just replace your code with the line below:

您可以Copy与PasteRanges使用1行代码表（和工作簿）之间，刚好与线下更换您的代码：

x.Sheets(1).Range("A1").CurrentRegion.Copy y.Sheets("DTR").[a1] 

If you want to use the PasteSpecialmethod, you got to do it in 2 lines, but you need to add a parameter after the PasteSpecial, like xlValues, etc.

如果你想使用这个PasteSpecial方法，你必须在 2 行中完成，但是你需要在 之后添加一个参数PasteSpecial，比如xlValues，等等。

x.Sheets(1).Range("A1").CurrentRegion.Copy
y.Sheets("DTR").[a1].PasteSpecial xlValues

回答by ThunderFrame

The problem with your original code was two-fold.

您原始代码的问题有两个方面。

1. The Cells.Deletestatement appeared afterthe copy, but a delete action clears the clipboard.

2. The Paste method is a member of the Sheet object, not the Range object.

1. 该Cells.Delete语句出现在复制之后，但删除操作会清除剪贴板。

2. Paste 方法是 Sheet 对象的成员，而不是 Range 对象。

Adjusting your code, that becomes:

调整你的代码，变成：

y.Sheets("DTR").Cells.Delete
x.Sheets(1).Range("A1").CurrentRegion.Copy
y.Sheets("DTR").Paste y.Sheets("DTR").[a1]

回答by tjb1

Change it to y.Sheets("DTR").[a1].PasteSpecial

将其更改为 y.Sheets("DTR").[a1].PasteSpecial

Pastedoes not work on ranges, to paste a range you have to use PasteSpecial. I believe by default it does everything but you can specify quite a bit, see here

Paste不适用于范围，要粘贴范围，您必须使用PasteSpecial。我相信默认情况下它可以完成所有操作，但您可以指定很多，请参见此处