Move a row to a different sheet based on a cell value

[pyclen]

Hi there,

to quote to Dr. Strange (not verbatim though), Dormammu, I have come again for advice.

I have a tracker w/multiple sheets, one is called NEW, one is Completed and one is Cancelled, there is also a lists tab for drop down menus. This is a mockup of a file I am using for work.

My question is:
Can I move a row where I have flipped the status from NO to either Yes, or Cancelled to the corresponding tab, i.e. when I set the cell in N2 to Yes, the whole row should move to COMPLETED, or if N2 = Cancelled to the Cancelled tab.
I also need to have the rows insert beneath the last row on either the Completed or Cancelled tab. I have both other tabs copied below (1st is completed, 2nd is cancelled, just look at column N if unclear)

And finally, when the row gets moved I would like to get rid of the formatting in column B which is based on a priority

Thank you again for helping out a noob who is trying to improve processes left behind.

example tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date received	Priority	Company	BD Contact	Lead	Quotation Number	SF Inquiry	Project decription	Type	Rebid	Target completion date	Actual completion date	Notes	Completed2
2	20-Jun-22	1	abc Ltd	Mike	John	Q123456	75-1234	make cars	quotation	no	25-Jun-22		make enough cars	No
3	20-Jun-22	2	dfg inc.	Ashley	John	Q123457	75-1235	make dummies	quotation	yes	28-Jun		make enough dummies	No
4	21-Jun-22	1	dfg inc.	Ashley	John	Q123458	75-1236	make engines	quotation	no	30-Jun		make enough engines	No
5	22-Jun-22	1	jung llc	Heidi	Cassiopeia	Q123459	75-1237	make seats	quotation	no	29-Jun		make enough seats	No
6	23-Jun-22	2	lan llc	Larry	Megan	Q123460	75-1238	make windows	quotation	no	24-Jun		make enough windows	No
7	24-Jun-22	2	abc Ltd	Mike	John	Q123461	75-1239	make wheels	quotation	no	27-Jun		make enough wheels	No
8	25-Jun-22	on hold	abc Ltd	Mike	John	Q123462	75-1240	make more cars	Cost Estimate	no	29-Jun		make way more cars	No
New

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Cell Value	="on hold"	text	NO
B2:B8	Cell Value	=2	text	NO
B2:B8	Cell Value	=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
N2:N8	List	=Completion
D2:D8	List	=BD
E2:E8	List	=Lead
I2:I8	List	=Type

Date received	Priority	Company	BD Contact	Lead	Quotation Number	SF Inquiry	Project decription	Type	Rebid	Target completion date	Actual completion date	Notes	Completed
15-Jun-22	1	abc Ltd	Mike	John	Q123456	75-1234	make cars	quotation	no	19-Jun-22	19-Jun	make enough cars	Yes

Date received	Priority	Company	BD Contact	Lead	Quotation Number	SF Inquiry	Project decription	Type	Rebid	Target completion date	Actual completion date	Notes	Complete
15-Jun-22	1	zxT, LLC	Ashley	Megan	Q123456	75-0023	make rubber hose	quotation	no	19-Jun-22	19-Jun	make enough cars	Cancelled

 

[My Aswer Is This]

So, you're saying if Range("N2").value says "COMPLETED" you want this row copied to a sheet Named "Completed" Or if Cancelled copy Row to sheet named "Cancelled"

And this will only apply to Range ("N2")
Never N4 or N5

Is this correct?
And do not copy formatting

And this script runs automatically when you enter the value into Range("N2")

 

[pyclen]

So, you're saying if Range("N2").value says "COMPLETED" you want this row copied to a sheet Named "Completed" Or if Cancelled copy Row to sheet named "Cancelled"

And this will only apply to Range ("N2")
Never N4 or N5

Is this correct?
And do not copy formatting

And this script runs automatically when you enter the value into Range("N2")

What I want to do is when I flip the cell in column N (can be 2, 3, 4 etc.) to either completed or cancelled that the whole row (A2-N2 or A4-N4 etc) moves to the corresponding tab.

Additionally, when the whole row moves that all formatting is removed although that is secondary, the whole row moving to a different tab is more important.

Hope this helps

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter Completed or Cancelled in Column N the script will run

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/20/2022  10:32:12 PM  EDT
If Target.Column = 14 Then
On Error GoTo M
Dim Lastrow As Long
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
Dim ans As String
r = Target.Row
ans = Target.Value
Lastrow = Sheets(ans).Cells(Rows.Count, "N").End(xlUp).Row + 1
Rows(r).Copy: Sheets(ans).Rows(Lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
'If you want the original row deleted from the original sheet remove the ' from in front of next line of code
'Rows(r).delete
End If
Exit Sub
M:
MsgBox "We had a problem " & vbNewLine & "You entered " & Target.Value & vbNewLine & "There is no sheet by that name"
End Sub

 

[pyclen]

Thank you very much as that indeed does the trick. The only change I need is to have the message box/error not appear when something else is chosen in Column N.
My choices are in progress, on hold, completed and cancelled, and with the above script I get errors when I select in progress or on hold.

When I select either in progress or on hold I do not need to have anything done, only with completed and cancelled do I need to have the rows moved.

I tried to remove the line with on error goto M as well as the lines below w/M: and the MSgBox but it gives an error in row 9 (starts w/ LastRow=......)

Again, thank you for the help, really appreciated

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/21/2022  9:31:35 AM  EDT
If Target.Column = 14 Then
On Error GoTo M
Dim Lastrow As Long
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
Dim ans As String
r = Target.Row
ans = Target.Value
Select Case Target.Value
Case "Completed", "Cancelled"
Lastrow = Sheets(ans).Cells(Rows.Count, "N").End(xlUp).Row + 1
Rows(r).Copy: Sheets(ans).Rows(Lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
'If you want the original row deleted from the original sheet remove the ' from in front of next line of code
'Rows(r).delete
End Select
End If
Exit Sub
M:
MsgBox "We had a problem " & vbNewLine & "You entered " & Target.Value & vbNewLine & "There is no sheet by that name"
End Sub

 

[pyclen]

Thank you much, sir, I really appreciate the help, and I am amazed with the skills of people on this board

great resource and board

 

[My Aswer Is This]

Thank you much, sir, I really appreciate the help, and I am amazed with the skills of people on this board

great resource and board

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.