VBA code only works once

[govi]

Hi,

I have VBA code that moves a row from my "todo" sheet to another sheet as soon as I fill in "finished".


The problem is that it doesn't work very well. It only works when I restart Excel. When I fill in "finished" after I use other macro's or Autofilter it doesn't work anymore.


I have to close Excel an start Excel again for it to work.


This is the code I use:

'Private Sub Worksheet_Change(ByVal Target As Range)

'afgewerkte taak naar archief verplaatsen

Application.EnableEvents = False

If Target.Cells.Count > 1 Then Exit Sub

If Target.Column <> 12 Then Exit Sub

If Target = "Finished" Then

Range("D" & Target.Row & ":L" & Target.Row).Copy Sheets("Archief").Cells(Rows.Count, 1).End(xlUp).Offset(1)

Range("C" & Target.Row & ":L" & Target.Row).ClearContents

End If

Application.EnableEvents = True

Sheets("Archief").UsedRange.Replace "Finished", Date'


Can anyone help?


Thanks,


govi

 

[Hui]

Govi

Give this a go

[pre]

Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 12 Then Exit Sub
If Target.Text = "Finished" Then
Range(Cells(Target.Row, 4), Cells(Target.Row, 12)).Copy Sheets("Archief").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Range(Cells(Target.Row, 3), Cells(Target.Row, 12)).ClearContents
End If

Sheets("Archief").UsedRange.Replace "Finished", Date '

End Sub

[/pre]

 

[govi]

Yep it works,


Thanks Hui!