Worksheet Change VBA Code

[kcpettit13]

Hello,

I am trying to automatically move an entire row of data to a different sheet based on changing text in a certain column i.e. entering "YES" in column I. I am new to writing code. The code I wrote seems to be skipping some steps. Can anyone help?

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sent_to_Tox").Select
ActiveSheet.Range("I2", ActiveSheet.Range("I2").End(xlDown)).Select
If ActiveCell.Text = "YES" Then
Selection.EntireRow.Cut
Sheets("Completed").Select
ActiveSheet.Range("LastCell").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sent_to_Tox").Select
ActiveCell.Select
Selection.EntireRow.Delete
End If


End Sub

 

[Norie]

Which sheet do you want to move the row to?

Where on that sheet should the row go?

 

[kcpettit13]

I want to move the row to the sheet titled "completed". I have a named range in that sheet to designate the last row because data is constantly being added to the sheet and I always want the row to move to the last row.

 

[Norie]

So this code is for the sheet 'Sent_to_Tox'?

If that's the case all you should need is something like this.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 9 Then
        If Target.Value = "YES" Then
            Application.EnableEvents = False
            Target.EntireRow.Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Target.EntireRow.Delete
            Application.EnableEvents = True
        End If
    End If

End Sub

 

[kcpettit13]

I was using this code, but the loop took way to long to run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xrow As Long
xrow = 2
Sheets("Need_to_Complete").Select
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row


Do Until xrow = lastrow + 1
ActiveSheet.Cells(xrow, 8).Select
If ActiveCell.Text = "YES" Then
Selection.EntireRow.Cut
Sheets("Completed").Select
ActiveSheet.Range("LastCell").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Need_to_Complete").Select
ActiveCell.Select
Selection.EntireRow.Delete
xrow = xrow - 1
End If

xrow = xrow + 1
Loop



End Sub

 

[kcpettit13]

That worked! Thank you!

One other question... if I wanted the cell to change when I enter a date in stead of "yes" how would that change the code?