Excel VBA How to detect if something was pasted in a Worksheet

Question

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.

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.

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.

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.

Thank you for your help.

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

Answer 1

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

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

Answer 2

Worksheet_Change event will do the job if you add a formula into cell which will never be overwritten. Let's say your data are pasted into A1 cell and occupied 5 columns. So, enter below formula into 6. column and row 1.

=COUNTBLANK(A1:A1048576)

Now, you're able to handle/detect paste event ;)