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]

Please answer the question I posted above.
If you are not getting the Message Box, it doesn't matter what is in the code, because the code is not actually running at all!
So we need to figure out why first. The answers to the questions I asked will go a long way in determining that.

 

[EBe]

If you got that pop-up message box, we know that the VBA code has been enabled.


If you are not getting the message box when you manually update data on that sheet, that means the automated VBA code is NOT being triggered.
I suspect you have not placed it in the correct location.
Can you please confirm the name of the VBA module that you have placed this VBA code in?

I'm right clicking on the tab with the original table, named 'Active' and then clicking on view code. The highlighted module that opens is called 'Sheet1 (Active)'. Does this sound right?

 

[MARK858]

Does this sound right?

Yes, it does

Get the sheet name sorted (I still have it as Complete in the code below so change it if necessary)
Go to Run then click Reset
Run the ReEnableEvents code Joe4 posted in post number 6
Put the word Stop where it is in the code below
Change a single cell in Column J after row 3 to "Discharged" without the quotes
Step through with F8 after you get the message box to try and pinpoint what is happening (in particular if it jumps out at the If statement)

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Code is running"
Stop
' 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]

Yes, it does

Get the sheet name sorted (I still have it as Complete in the code below so change it if necessary)
Go to Run then click Reset
Run the ReEnableEvents code Joe4 posted in post number 6
Put the word Stop where it is in the code below
Change a single cell in Column J after row 3 to "Discharged" without the quotes
Step through with F8 after you get the message box to try and pinpoint what is happening

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Code is running"
Stop
' 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

Can you also confirm exactly what it is that you are doing to trigger this code to run?
What value are you entering exactly, and in what cell (provide exact cell address)?

 

[EBe]

Yes, it does

Get the sheet name sorted (I still have it as Complete in the code below so change it if necessary)
Go to Run then click Reset
Run the ReEnableEvents code Joe4 posted in post number 6
Put the word Stop where it is in the code below
Change a single cell in Column J after row 3 to "Discharged" without the quotes
Step through with F8 after you get the message box to try and pinpoint what is happening (in particular if it jumps out at the If statement)

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Code is running"
Stop
' 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

Okay, I’ve completed your suggestions. Now when I make changes to the sheet, the VBA box pops up with the ‘stop’ highlighted in yellow. Any idea what this means?

 

[EBe]

Can you also confirm exactly what it is that you are doing to trigger this code to run?
What value are you entering exactly, and in what cell (provide exact cell address)?

I’m writing ‘Discharged’ (not with the quotation marks) in column J row 11

 

[MARK858]

Any idea what this means?

You need to keep hitting the F8 button (or under the Debug icon click on Step Into) and it will step through the lines one at a time so you can see whether all the parts are being executed (if you size the window right you can also watch it on the sheet), in particular what happens after the line starting with If.

BTW, do you really want the copied data pasting in column J in the destination sheet?

 

[EBe]

You need to keep hitting the F8 button (or under the Debug icon click on Step Into) and it will step through the lines one at a time so you can see whether all the parts are being executed (if you size the window right you can also watch it on the sheet), in particular what happens after the line starting with If.

BTW, do you really want the copied data pasting in column J in the destination sheet?

Okay, I think we’re getting somewhere now. So when I keep hitting F8, the row moves as it’s meant to. It doesn’t do this automatically - only after I’ve hit F8 a few times.
Also, the row is being moved to the right sheet, but it’s placing it underneath the table rather than in it. Is it easier for me to just not format this sheet as a table?

 

[MARK858]

Is it easier for me to just not format this sheet as a table?

Probably if you don't need it to be a table

 

[EBe]

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?