Cut Rows and paste in new sheet when cell meets certain condition

[griggsa14]

Hello,

I was wondering how to right a code to cut an entire row and paste in another sheet when one cell meets a specific criteria. I have a checklist in column A starting in A2 that has the options -, Pending, Completed, Denied, Mistake-Abandoned. I also have 5 tabs labeled Overview, Pending, Completed, Denied, Mistake-Abandoned going left to right. The dropdown in column A is in every cell individually from A2 all the way to the end of the sheet. I would like it so that when someone switches the dropdown selection to completed in any of the A column that it automatically cuts the whole row from the pending sheet and pastes into the Completed sheet. The headers are the same in both sheets across the top row from A-K. I would also like it so that when it pastes into the new worksheet it goes to the next available open row so that it does not delete any old information. Ideally this would all happen automatically as well. Any help I get would be greatly appreciated and if you need more information let me know.

Thank you

 

[griggsa14]

Do you know a way instead of localizing it and then relying on copying it down that I can just select the whole column in that code excluding H1?

 

[JoeMo]

Do you know a way instead of localizing it and then relying on copying it down that I can just select the whole column in that code excluding H1?

Change your code to include the range you want in col H. Follow the example in the code I posted, except use col H instead of col A.

 

[griggsa14]

I am back. The tabs I have from left to right are Overview, Pending, Completed, Denied, Mistake-Abandoned. I need to be able to add steps as they progress all localized to one cell. I used data validation for the dropdown and was able to get it working in cell I2 but have been unable to make that coding go all the way down column I. I need it to be in every cell in column I with the same dropdown options. The columns are A-N. Also would you be able to help me adjust the code that you created to cut and past completed for Mistake-Abandon and Denied as well? Ideally the only options from the first dropdown that would stay in the pending tab are the pending options as well as if it were blank. The other options should all ideally go to their corresponding sheets. I put a representation of my sheet below with the coding I have in there currently. I appreciate your help.




[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Partner[/TD]
[TD]Property[/TD]
[TD]Applicant[/TD]
[TD]Roommates[/TD]
[TD]App. Started[/TD]
[TD]App. Date[/TD]
[TD]Lease Date[/TD]
[TD]Steps[/TD]
[TD]Notes[/TD]
[TD]Rent Amount[/TD]
[TD]Approval[/TD]
[TD]Approval Time Stamp[/TD]
[TD]Purchase Date[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 87"]
<tbody>[TR]
[TD]-[/TD]
[/TR]
[TR]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]Pending- Awaiting Documents[/TD]
[/TR]
[TR]
[TD]Pending- Awaiting Lease Details[/TD]
[/TR]
[TR]
[TD]Pending- Quote Sent[/TD]
[/TR]
[TR]
[TD]Pending- Invoice Sent[/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Denied[/TD]
[/TR]
[TR]
[TD]Mistake-Abandoned[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="class: cms_table, width: 87"]
<tbody>[TR]
[TD]-[/TD]
[/TR]
[TR]
[TD]Partner Emailed[/TD]
[/TR]
[TR]
[TD]Resident Emailed[/TD]
[/TR]
[TR]
[TD]UW Summary Sent[/TD]
[/TR]
[TR]
[TD]Agreement Sent[/TD]
[/TR]
[TR]
[TD]Invoice Sent[/TD]
[/TR]
[TR]
[TD]Bond Sent[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, i As Long
Set R = Intersect(Range("A:A"), Target)
If Not R Is Nothing Then
For i = R.Count To 1 Step -1
If R(i).Value = "Completed" Then
Application.EnableEvents = False
With R(i).EntireRow
.Copy Destination:=Sheets("Completed").Cells(Sheets("Completed").Rows.Count, _
"A").End(xlUp).Offset(1, 0)
.Delete
End With
End If
Next i
End If
Application.EnableEvents = True




Dim Oldvalue As String
Dim Newvalue As String


On Error GoTo Exitsub
If Target.Address = "$I$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub