Hi all
I have an issue with worksheet change (an audit tool) that copies a range to the next row. It seems to fire before the first event ends
I have a sheet with columns B to L that I want to copy down 1 row. Four cells have formulas. I do not want any data copied.
If I disable my worksheet change event the copy paste works fine. But if its active it seems to fire before the CLEAR cells of the first event happens. It then goes to error 13 type mismatch

My two codes are:
1. The copy paste and 2. the audit fired by worksheet change.
Any help please?
Please Login or Register  to view this content.
Sub but_insert_row_formulas()
'module 7
'copy formulas from row 6 to new row
Dim lastROw As Long
Dim NewRow As Long
Dim r As Range

ActiveSheet.Unprotect Password:="PS" 'turn off protection to allow copy
Application.ScreenUpdating = False
lastROw = Range("B6").End(xlDown).Row
NewRow = lastROw + 1
With Range("B" & lastROw & ":L" & lastROw)
.Copy
.Offset(1).PasteSpecial xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.Offset(1).PasteSpecial xlPasteFormulas
.Offset(1).PasteSpecial xlPasteFormats

End With
Range("B" & NewRow & ":c" & NewRow).Clear
Range("G" & NewRow & ":K" & NewRow).Clear

Range("B" & NewRow & ":L" & NewRow).Locked = False

Range("A" & NewRow).Select

ActiveSheet.Protect Password:="PS" 'turn ON protection
Application.ScreenUpdating = True

End Sub
Please Login or Register  to view this content.
and the audit part that causes the error 13:

Please Login or Register  to view this content.
Dim i As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
'log update If they change a value then record who , what and when in teh log sheet
Set ws = Sheets("log")

Sheets("Log").Unprotect Password:="sp" 'open log sheet and unprotect to allw addition

i = ws.Range("B" & Rows.Count).End(xlUp).Row + 1

If target.Value <> PreviousValue Then
With ws


.Range("A" & i).Value = Application.UserName
.Range("B" & i).Value = "TIS Freezer"
.Range("C" & i).Value = target.Address
.Range("D" & i).Value = PreviousValue
.Range("E" & i).Value = target.Value
.Range("F" & i).Value = Format(Now(), "dd/mm/yyyy, hh:mm:ss")

End With
End If
Sheets("Log").Protect Password:="sp" 'protect the log sheet again
Application.ScreenUpdating = True
Private Sub Worksheet_SelectionChange(ByVal target As Range)
PreviousValue = target.Value
Please Login or Register  to view this content.