Move Row to Another Sheet Based on Value

[kjw298]

Hi guys,

This is my first post and hoping someone can help me as it is driving me CRAZY!!
Just to throw a disclaimer out there, I am doing this all by googling and have no experience in VBA whatsoever.

I want to move a row to another sheet after 'Yes' has been entered into a cell in column 'M' but I want it to be done automatically as soon as 'Yes' is selected in the data validation list that's being used.
I have used the below code on another file earlier on in the week and it works perfectly but will not work for the file I'm working on for some reason. Is there something in Excel that I need to select to get it to do it automatically or is there something I have missed out when changing the code to reflect the file I'm working on?

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 And Target.Cells.Count = 1 Then
If LCase(Target.Value) = "Yes" Then
With Target.EntireRow
.Copy Sheets("Complete").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Delete
End With
End If
End If
End Sub

Thanks so much for anyone that is able to help, I am about to pull may hair out

 

[Joe4]

Welcome to the Board!

This line will NEVER evaluate to true:

If LCase(Target.Value) = "Yes" Then

it should be:

If LCase(Target.Value) = "yes" Then

(as if you take the lower case of any word, you cannot have any capital letters in the result!)

 

[kjw298]

Oh wow, as simple as that, that has made it work.
Thank you so so much

 

[kjw298]

For some reason, when I mark one as 'yes' it moves to the other sheet but when I do another one, it overwrites it

 

[kjw298]

How do I stop this happening?

 

[Joe4]

For some reason, when I mark one as 'yes' it moves to the other sheet but when I do another one, it overwrites it

I am guessing that column "A" in the row you are copying over is probably blank then.
Is that correct?
If so, can you tell us a column that will ALWAYS have data in it?

Whatever column that is, change the "A" in this formula to that column letter:

.Copy Sheets("Complete").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

 

[kjw298]

Yes it's blank, Column B will always have info in. I changed the code I get an error message saying 'You can't paste this here because the copy area and paste area aren't the same size'

 

[Joe4]

Whoops, forgot then that we need to Offset to move the first cell to column A, i.e.

.Copy Sheets("Complete").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)

 

[kjw298]

Perfect!! Thank you again

 

[Joe4]

You are welcome.