Move Row to new sheet if Y entered in Column L

[ryanf88]

Hello

I was wondering how to move a row if Y or y was entered in column L. The row will deleted from sheet1 and moved to sheet2.

I would like this code to run when ever there is a change I believe the code is Sub Workbook_Change or something, but I never got anything to work.

Here is a link to the file if that helps. Row L would be the column to be looking for a Y or y. Not sure if this maters, but Row L is set to hidden (so it wont print off) I did this under format cells - protection - then the check box for hidden. As well the top 4 lines are Locked so needs to start on the 5th line.

http://www.filedropper.com/prioritylisttest


Thank-you for the help in advance


So far i did this but it doesnt work for me and does not only look for Y or y.

Private Sub Workbook_Change()

If Not Intersect(Target, Range("L")) Is Nothing Then
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
LR1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row
drow = 5
For j = 5 To LR1
If sh1.Range("L" & j) <> "" Then
sh1.Rows(j).Copy sh2.Range("A" & drow)
sh1.Rows(j).Delete
j = j - 1
drow = drow + 1
End If
Next
End Sub

 

[CodeNinja]

In the sheet 1 change event add this code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Endme
If Target.Column = 12 And UCase(Target.Value) = "Y" Then
    Sheet1.Cells(Target.Row, 1).EntireRow.Cut
    Sheet2.Range("A1").Insert Shift:=xlDown
End If
Endme:

End Sub

 

[ryanf88]

CodeNinja

If i use that code the line does not delete from the first sheet, other than that it seems to work great. How would i go about adding in that i tried Sheet1.Cells(Target.Row,1).delete but nothing happens with that.

Thank-you

 

[CodeNinja]

Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 12 And UCase(Target.Cells(1).Value) = "Y" Then
    Sheet1.Cells(Target.Row, 1).EntireRow.Copy
    Sheet2.Range("A1").Insert Shift:=xlDown
    Sheet1.Range("A" & Target.Row).EntireRow.Delete
End If



End Sub

 

[ryanf88]

One more quick question. I did not realize this would be very helpful until I started using this formula.

Is there a way to copy the row and past it to sheet 2 just like now, but when deleting the row only delete the row from column C and on. Including Column C.
I just have priority codes like A1, A2, A3, etc, B1 B2 B3 so i want the A's to all move up one and the Bs to stay the same.
I dont know if this is a huge task or not.

 

[CodeNinja]

One more quick question. I did not realize this would be very helpful until I started using this formula.

Is there a way to copy the row and past it to sheet 2 just like now, but when deleting the row only delete the row from column C and on. Including Column C.
I just have priority codes like A1, A2, A3, etc, B1 B2 B3 so i want the A's to all move up one and the Bs to stay the same.
I dont know if this is a huge task or not.

So, you dont actually want to delete the row, you just want to delete the data from Column C - last used column?

I would use the latest version and change the following line:

Sheet1.Range("A" & Target.Row).EntireRow.Delete
To:
Sheet1.Range("C" & Target.Row & ":BA" & target.row)..ClearContents


That assumes you do not go past column BA... if you do, adjust to whatever you think would be enough.... You could dynamically program the last column too if you want.

 

[ryanf88]

that is close to what I would like the file to do. Only thing i am trying to do now is not have a blank row.

For example row 5 is all filled out and i put Y. The whole row copies to sheet 2. and on sheet 1 the A1 would stay (Priority) but the rest would be deleted and the other row would move up to fill in the space that was left blank when the row was deleted.

 

[CodeNinja]

I'm not sure what you mean... can you please post a before and after example?

 

[ryanf88]

here is a file with the before and after priorities.
I am not sure if it is an easy code, but if it is something that takes a lot i will move the A1 and A2 and such myself and use the code that you gave me the other day which does work well.

I highlighted the rows that changed in column M with yellow to make it easier to see what happened. There is the after priorities on the 3rd sheet.

http://www.filedropper.com/prioritylisttest

Thank-you

 

[CodeNinja]

Sorry, I dont go to websites to pick up the file. I am at work, and prefer to keep web browsing to a minimum. Post example please.