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

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.

I was unable to add this as a comment so I'm posting this as an answer. @Nahbyr 's answer works when excel has "English" set as it's preferred language, otherwise it won't work.

So after manually searching using the immediate window I was able to find out the appropiate indexes for it to work on every language.

This is the function I wrote to test if the last action was a paste action, paste or paste special.

Public Function LastActionPaste() As Boolean

' The function LastActionPaste checks if the last action made was a paste action, if so it returns TRUE
' Otherwise it returns FALSE

Dim UndoList As String

LastActionPaste = False
UndoList = Application.CommandBars(11).Controls(14).List(1)

'~~> Check if the last action was a paste or paste special
If UndoList = "Paste" Or UndoList = "Paste Special" Then
    
    LastActionPaste = True
    
End If

End Function

UPDATE

So apparently the indexes are not the same on different installations of Excel, whether because they are different versions or whatsoever...

So even if the preferrred language is not English, the CommandBars.Name is still in english, BUT the Controls.Caption do change...

Now I hope that the Controls indexes do not change otherwise this won't work.

So I modified the function like this for it to work:

Public Function LastActionPaste() As Boolean

' The function LastActionPaste checks if the last action made was a paste action, if so it returns TRUE
' Otherwise it returns FALSE

Dim UndoList As String
Dim barFound As Boolean
Dim index As Long

LastActionPaste = False
index = 1
barFound = False

Do While barFound = False
    
    If Application.CommandBars(index).name = "Standard" Then
        
        barFound = True
        
        Else
        
        index = index + 1
        
    End If
    
Loop

UndoList = Application.CommandBars(index).Controls(14).List(1)

'~~> Check if the last action was a paste or paste special
If UndoList = "Paste" Or UndoList = "Paste Special" Then
    
    LastActionPaste = True
    
End If

End Function

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 ;)