Issues When Using VBA to Automatically Move Row to Another Sheet Based On Cell Value

[EBe]

Hi,

I’m completely new to VBA and have previously used this site to find a code to automatically move a row to another sheet in excel based on cell value. It was all working fine until I upgraded my computer and transferred my files over, and for some reason it seems to have stopped working. I've tried to work it out but am completely lost!

I've opened VBA and inserted a new module using the code below but nothing seems to happen. I've tried to run the module, but no module appears when I click 'run'. The code is exactly the same as I was using before so I'm assuming it's user error somewhere. If anyone can point me in the right direction I'd be really grateful.

For reference, I'm trying to move a row of data from sheet 'active' to a new sheet 'processed' when the value in column J reads 'discharged'.

The code I’m using is:

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column J after row 3 and is set to "Discharged"
If Target.Column = 10 And Target.Row > 3 And Target.Value = "Discharged" Then
Application.EnableEvents = False
' Copy columns A to J to Processed sheet in next available row
Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Copy Sheets("Complete").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub

 

[Joe4]

Thanks so much everyone for all your help. It’s pretty much running as it should now, hooray!

How do I get the message box which tells me ‘code is running’ to stop popping up every time I change something on the workbook?

Just delete that line that starts with "MsgBox" from your VBA code.

Glad to hear you got it all working now, and you hopefully learned some new VBA debugging techniques!

 

[EBe]

Just delete that line that starts with "MsgBox" from your VBA code.

Glad to hear you got it all working now, and you hopefully learned some new VBA debugging techniques!

Thank you so much. Really appreciate all the help, I’m learning slowly!