Excel VBA如何检测是否在工作表中粘贴了某些内容 [英] Excel VBA How to detect if something was pasted in a Worksheet

问题描述

我将首先说我的Excel和VBA经验仅限于我在学校看到的内容。我有编程经验，但在其他语言。

I'll start by saying that my experience with Excel and VBA is limited to what I saw in school. I have programming experience, but in other languages.

我有一个文件，我得到每周。此文件的结构始终相同：
ID，名称，日期，1到4之间的值，不相关的数据。

I have a file that I get every week. The structure of this file is always the same: ID, Name, Date, Value between 1 and 4, non-relevant data.

选择此数据通过选择所有按钮（工作表的左上角，MS excel 2013中的单元格下方的小三角形），然后复制到另一个默认文件中，该文件将根据1-4的不同页面显示和过滤数据价值和日期。

This data is selected through the 'select all' button (top left corner of the worksheet, little triangle below the cellname in MS excel 2013) and then copied into another default file that reworks the data to show and filter it in different sheets based on the 1-4 value and the date.

我的问题：如何检测数据是否被粘贴？我尝试过Worksheet.Change事件，但是粘贴命令（CTRL + V）不会触发Change事件。
另外，如何复制数据？它会逐行更新，逐个单元格（哪个方向），...？
我知道，一旦我可以检测到复制命令，我可以通过调试来轻松找到最后一个问题的答案，但是你不知道有人知道答案。

My question: How do I detect when data has/is being pasted? I've tried the Worksheet.Change event, but the paste command (CTRL+V) does not trigger the Change event. Also, how will the data be copied? Will it update Row by row, cell by cell (which direction), ...? I know I can easily find the answer to the last question by debugging it once I can detect the copy command, but you never know if someone knows the answer.

有另外一个更容易（或更好）的方法吗？

Is there another, more easy (or better) way to do this?

如果需要，可以给出更多的数据和信息。

More data and information can be given if needed.

感谢您的帮助。

编辑：'...有/正在复制？

'...has/is being copied?' changed to pasted as it should've been.

推荐答案

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim UndoList As String

  '~~> Get the undo List to capture the last action performed by user
  UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

  '~~> Check if the last action was not a paste nor an autofill
  If Left(UndoList, 5) = "Paste" Then
    'Do stuff
  End If
End Sub

这样做了。对于那些需要类似的东西，知道他们的列表的大小@MaciejLos的答案也可以。

This did the trick. For those who need something similar and know the size of their list @MaciejLos' answer would also work.

这篇关于Excel VBA如何检测是否在工作表中粘贴了某些内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！