How to be able to copy/paste more than 1 cell at a time when macro for track changes is enabled?

[powerpivot beginner]

Hi,

I have a macro to keep track of changes in an excel sheet. However, it is preventing
me from copying and pasting more than 1 cell at a time. Please advise how to overcome the problem.




Option Compare Text

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)



Const sRng As String = "A1:BJ4000" ' change as required

Dim sOld As String

Dim sNew As String

Dim sCmt As String

Dim iLen As Long

Dim bHasComment As Boolean



With Target(1)

If Intersect(.Cells, Range(sRng)) Is Nothing Then Exit Sub

sNew = .Text

Application.EnableEvents = False

Application.Undo

sOld = .Text

.Value = sNew

Application.EnableEvents = True

.Interior.Color = vbGreen


sCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & Application.UserName & Chr(10) & "Previous Text :- " & sOld




If Target(1).Comment Is Nothing Then

.AddComment

Else

iLen = Len(.Comment.Shape.TextFrame.Characters.Text)

End If



With .Comment.Shape.TextFrame

.AutoSize = True

.Characters(Start:=iLen + 1).Insert IIf(iLen, vbLf, "") & sCmt

End With

End With

End Sub