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]

I've opened VBA and inserted a new module using the code below but nothing seems to happen.

You are putting this code in the wrong place. "Worksheet_Change" event procedure are VBA code that runs automatically when a cell is updated, but it needs to be put in the proper place in order to run automatically. It needs to go in the proper sheet module.

An easy way to get there is to go to the sheet you want to run this against, right-click on the sheet tab name at the bottom of the page, select "View Code", and then paste this code in the VB Editor that pops up. This is where it needs to go to run automatically.

 

[EBe]

You are putting this code in the wrong place. "Worksheet_Change" event procedure are VBA code that runs automatically when a cell is updated, but it needs to be put in the proper place in order to run automatically. It needs to go in the proper sheet module.

An easy way to get there is to go to the sheet you want to run this against, right-click on the sheet tab name at the bottom of the page, select "View Code", and then paste this code in the VB Editor that pops up. This is where it needs to go to run automatically.

Thanks so much for your reply, I knew I was doing something wrong. Unfortunately, it’s still not working when I enter it the way you’ve said. Could there be an issue with the code?

 

[Joe4]

Thanks so much for your reply, I knew I was doing something wrong. Unfortunately, it’s still not working when I enter it the way you’ve said. Could there be an issue with the code?

If the code works in the previous place, and you have not altered it at all, it should also work in the new place, if:
1. you have enabled VBA/macros on your new workbook
- and -
2. all the conditions are exactly the same (and the structure is exactly the same)

Tell me exactly what you are entering into what cell that it should trigger this code to run.

Also note, if you "partially" ran this code, you may have disabled event procedure code from running and may need to re-enable it to get it to run again.
You would do that by manually running this short procedure:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

It does not matter which module you place this code in, as long as you run it manually.

 

[EBe]

It's definitely a .xlsm workbook so VBA should be enabled. I've tried running the procedure above but still nothing.

My workbook tab (Active) is a list of patient data going from column A to J (name, referral pathway, discharge date etc). When the text in column J reads 'Discharged', I want all of the data from that row to be deleted and appear in my second tab which is named 'Processed'. Does this make sense? If not, I can take out personal information and upload it. I think my code must be wrong somewhere, but I'm not sure where!

 

[Joe4]

It's definitely a .xlsm workbook so VBA should be enabled. I've tried running the procedure above but still nothing.

That has nothing to do with whether the VBA code is enabled or not. Enabling VBA Code/Macros is separate of the file extension.
You can have your Excel set-up to disable ALL VBA code, regardless of the file extension.

2. all the conditions are exactly the same (and the structure is exactly the same)

Can you please confirm this?

One thing you can do to make sure that your automated event code is actually running is by placing a simple message box at the beginning of your code (remember, you don't manually "run" event procedure code, it is automatically triggered to run IF you have placed it in the correct location and IF you have named it properly).

So add something like this to your code (line in red):

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Code is running"

' 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

Then, if you manually make any addition or edit to the data on that sheet, you SHOULD get a pop-up message saying "Code is running".
If you do not, then something is not right. The most likely issues would be:
1. You have placed the VBA code in the wrong location (it HAS to be in the SHEET module of the sheet you want it to run against).
2. VBA code has been disabled
3. Events have been disabled

To make sure that events have been enabled, you can run this little procedure manually:

Sub ReEnableEvents()
    Application.EnableEvents = True
    MsgBox "Events are enabled!"
End Sub

If you manually run that successfully, you should get a message saying "Events are enabled!".

 

[EBe]

That has nothing to do with whether the VBA code is enabled or not. Enabling VBA Code/Macros is separate of the file extension.
You can have your Excel set-up to disable ALL VBA code, regardless of the file extension.


Can you please confirm this?

One thing you can do to make sure that your automated event code is actually running is by placing a simple message box at the beginning of your code (remember, you don't manually "run" event procedure code, it is automatically triggered to run IF you have placed it in the correct location and IF you have named it properly).

So add something like this to your code (line in red):

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Code is running"

' 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

Then, if you manually make any addition or edit to the data on that sheet, you SHOULD get a pop-up message saying "Code is running".
If you do not, then something is not right. The most likely issues would be:
1. You have placed the VBA code in the wrong location (it HAS to be in the SHEET module of the sheet you want it to run against).
2. VBA code has been disabled
3. Events have been disabled

To make sure that events have been enabled, you can run this little procedure manually:

Sub ReEnableEvents()
    Application.EnableEvents = True
    MsgBox "Events are enabled!"
End Sub

If you manually run that successfully, you should get a message saying "Events are enabled!".

Thanks for your reply. I've run the small code above and had a message box to say events are enabled so we know that's working.

How do I make sure VBA is enabled?

I've also added the line in red above to my code but nothing more is happening.

The conditions and structure are the same, aside from I did add an extra column into my table a while ago, but changed the code (where the letter I was in place, I changed this to J, as the column that needs to read 'Discharged' has moved along one). Maybe I've gone wrong with this somewhere, but can't see where.

 

[MARK858]

I'm trying to move a row of data from sheet 'active' to a new sheet 'processed'

The sheet it is moving the data to in the code is called "Complete" not "processed"

Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Copy Sheets("Complete").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)

 

[Joe4]

Thanks for your reply. I've run the small code above and had a message box to say events are enabled so we know that's working.

How do I make sure VBA is enabled?

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

I've also added the line in red above to my code but nothing more is happening.

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?

 

[EBe]

The sheet it is moving the data to in the code is called "Complete" not "processed"

Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Copy Sheets("Complete").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)

I was really hoping that this was the issue but it's still not working