宏从一个工作表复制并粘贴到另一个工作表

I have a workbook where I need to be able to click on a single cell of a worksheet and hit my command button. That copies and pastes the cell value to the first blank cell in column E on a different worksheet within the same workbook. When I just run the macro by itself, it works fine. But when I paste the code into a command button, it gives me a couple of runtime error 1004's. The most common error is "Select method of range class failed" and refers to the code line that tells it to select Range (E4). Here is the code:

我有一个工作簿,我需要能够单击工作表的单个单元格并点击我的命令按钮。它将单元格值复制并粘贴到同一工作簿中不同工作表上的E列中的第一个空白单元格。当我只是自己运行宏时,它工作正常。但是当我将代码粘贴到命令按钮时,它会给我一些运行时错误1004。最常见的错误是“选择范围类的方法失败”,并引用告诉它选择范围(E4)的代码行。这是代码:

Private Sub CommandButton1_Click()

' Choose player from Player list and  paste to Draft list.

    Sheets("Players").Select
    Selection.Select
    Selection.Copy

    Sheets("Draft").Select
    Range("E4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.PasteSpecial _ 
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

2 个解决方案

#1

TL;DR, couple options to resolve this, in order of preference:

TL; DR,按优先顺序解决此问题的两个选项:

Stop using Select to access cells

停止使用“选择”访问单元格

Qualify your call to Range("E4") when executing code in a Worksheet object by using Application.Range("E4") or Sheets("Draft").Range("E4") or ActiveSheet.Range("E4")

使用Application.Range(“E4”)或Sheets(“Draft”)在Worksheet对象中执行代码时,对Range(“E4”)的调用进行限定。范围(“E4”)或ActiveSheet.Range(“E4”)

Move the code to ThisWorkbook or a code module and call that Sub from the event.

将代码移动到ThisWorkbook或代码模块,并从事件中调用该Sub。

Here is the lengthy part that attempts to explain why your code does not work.

这是一个冗长的部分,试图解释为什么你的代码不起作用。

This all comes down to: where is the code executing? Different execution contexts will behave differently when you use unqualified references to Cells Range and a number of other functions.

这一切都归结为:代码在哪里执行?当您使用对“单元格范围”和许多其他函数的非限定引用时,不同的执行上下文将表现不同。

Your original code likely ran inside ThisWorkbook, a code module, or possibly in the code file for sheet Draft. Why do I guess this? Because in all of those places a call to Range("E4") would be acceptable to get the cell E4 on sheet Draft. Cases:

您的原始代码可能在ThisWorkbook,代码模块中运行,或者可能在表草稿的代码文件中运行。为什么我猜这个?因为在所有这些地方,可以接受对Range(“E4”)的调用以获取表格草稿上的单元格E4。案例:

ThisWorkbookand a code module will execute Range on the ActiveSheet which is Draft since you just called Select on it.

这个工作簿和一个代码模块将在ActiveSheet上执行Range,它就是Draft,因为你刚刚调用了Select。

Inside Draft will execute Range in the context of Draft which is acceptable since that is the ActiveSheet and the place where you are trying to get cell E4.

Inside Draft将在Draft的上下文中执行Range,这是可接受的,因为这是ActiveSheet以及您尝试获取单元格E4的位置。

Now what happens when we add an ActiveX CommandButton to the mix? Well that code is added to the Worksheet where it lives. This means that the code for the button can possibly execute in a different context than it did before. The only exception to this is if the button and code are both on sheet Drafts, which I assume not since you Select that sheet. For demonstrations, let's say the button is located on sheet WHERE_THE_BUTTON_IS.

现在,当我们向混合添加ActiveX CommandButton时会发生什么?那么代码被添加到它所在的工作表中。这意味着按钮的代码可能在与以前不同的上下文中执行。唯一的例外是如果按钮和代码都在工作表草稿上,我认为不是因为您选择该工作表。对于演示,假设该按钮位于工作表WHERE_THE_BUTTON_IS上。

Given that sheet, what is going on now? Your call to Range is now executed in the context of sheet WHERE_THE_BUTTON_IS regardless of the ActiveSheet or anything else you do outside of the call to Range. This is because the call to Range is unqualified. That is, there is no object to provide scope to the call so it runs in the current scope which is the Worksheet.

鉴于那张表,现在发生了什么?您对Range的调用现在在工作表WHERE_THE_BUTTON_IS的上下文中执行,无论ActiveSheet还是您在Range调用之外执行的任何操作。这是因为对Range的调用是不合格的。也就是说,没有对象为调用提供范围,因此它在当前作用域中运行,即工作表。

So now we have a call to Range("E4") in sheet WHERE_THE_BUTTON_IS which is trying to Select the cell. This is forbidden because sheet Draft is the ActiveSheet and

所以现在我们在表单WHERE_THE_BUTTON_IS中调用了Range(“E4”),它正在尝试选择单元格。这是禁止的,因为工作表草稿是ActiveSheet和

Thou shalt not Select a cell on a Worksheet that is not the ActiveSheet

您不能在工作表上选择不是ActiveSheet的单元格

So with all of this, how do we resolve this issue? There are a couple of ways out:

所有这一切,我们如何解决这个问题?有几种方法可以解决:

Stop using Select to manipulate cells. This gets away from the main problem here, quoted above. This assumes your button lives on the same sheet as the Selection to copy/paste.

停止使用选择来操纵单元格。这远离了上面引用的主要问题。这假设您的按钮与要复制/粘贴的选项位于同一工作表上。

Private Sub CommandButton1_Click()

    Sheets("Draft").Range("E4").End(xlDown).Offset(1).Value = Selection.Value

End Sub

Qualify the call to Range so that it executes in the proper context and chooses the right cell. You can use the Sheets("Draft").Range object to qualify this or Application.Range instead of the bare Range. I highly recommend option 1 instead of trying to figure out how to make Select work.

限定对Range的调用,使其在适当的上下文中执行并选择正确的单元格。你可以使用Sheets(“Draft”)。Range对象来限定它或Application.Range而不是裸Range。我强烈推荐选项1,而不是试图弄清楚如何使选择工作。

Private Sub CommandButton1_Click()
    Sheets("Players").Select
    Selection.Copy

    Sheets("Draft").Select

    'could also use Application.Range here
    Sheets("Draft").Range("E4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select

    Selection.PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Move the code back to a Sub that is outside of the Worksheet object and call it from the CommandButton1_Click event.

将代码移回到Worksheet对象外部的Sub,并从CommandButton1_Click事件中调用它。

#2

Private Sub CommandButton1_Click()
     Sheets("Draft").Range("E4").End(xlDown).Offset(1).Value2 = ActiveCell.Value2
End Sub

My motto: if it doesn't have a Select method it can't have a Select method error.
Fun fact: even if it's working properly, it will replace your existing values if E4 is empty. I recommend using LastRow instead (I like the Range("E:E").Findone the most).

我的座右铭:如果它没有Select方法,它就不会有Select方法错误。有趣的事实:即使它正常工作,如果E4为空,它将替换现有值。我推荐使用LastRow(我喜欢Range(“E:E”)。最多的是Findone)。

#1