VBA code for audit trail issue

Hi,

Not sure if this will be possible - I have a workbook in which vba is used to record changes made on one sheet onto another sheet called 'log'.

The code works fine when people make changes in cells one at a time but if they paste in data to more than one cell at once I get a run time error '13'.

So the code I am using on the sheet where the changes are is:

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value <> PreviousValue Then
        Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
            Application.UserName & " changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value
                     
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

The code on the log sheet is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Cells(Target.Row, 4).Value = Now
End If
End Sub

Any ideas how I can enable the changes to be logged if more than 1 cell is changed at once? Thanks in advance for anyone taking the time to look at this.