Move row to another tab when cell value equals "Completed"

[MissTLC]

Good morning,
I know there have been several questions around this subject and I have tried to amend the VBA code without success.

Here is how my worksheet is laid out:
Columns A-Q
Rows 2-186 (first row holds the headers)
I have several conditional formatting rules and lists within my spreadsheet.

What I need:
If Column B = "Completed" than move entire row to "Complete" tab next in line.

Any help is greatly appreciated. Unfortunately, my work computer doesn't allow me to have the Mini-Sheet add on but if you need to see my spreadsheet, please let me know.

Cheers!!!

 

[Bosquedeguate]

Please provide a copy of the sheet

You will need a VBA macro (sub) that move your record from one sheet to the other.

The macro will run on a worksheet_change event

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Completed" in any cell in column B and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 2 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Completed" Then
        Target.EntireRow.Copy Sheets("Complete").Cells(Sheets("Complete").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[MissTLC]

 

[MissTLC]

Thanks "mumps" that worked perfectly. Is there a way to put in two or more rules within the same VBA code? I would also like anything that says "Cancelled" to move to the same tab "Complete".

 

[Bosquedeguate]

Change the line of VBA Code that tests for "Completed" as follows:

If Target = "Completed" OR Target "Canceled" Then

I suggest that you make a Validation List for the Course Status column of data - this will restrict entries to only ones that you want. e.g. Completed, Canceled, Waiting List, Scheduled

 

[Bosquedeguate]

Change the line of VBA Code that tests for "Completed" as follows:

If Target = "Completed" OR Target "Canceled" Then

I suggest that you make a Validation List for the Course Status column of data - this will restrict entries to only ones that you want. e.g. Completed, Canceled, Waiting List, Scheduled

Opps

If Target = "Completed" OR Target = "Canceled" Then

 

[Bosquedeguate]

You can also use the Instr function to search for substrings (just in case you have others in the future).
If Instr("Completed, Canceled", Target) > 0 Then

 

[MissTLC]

Thanks so much for all your help, it worked like a charm )