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

MissTLC

New Member
Joined
Jul 14, 2023
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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!!!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
1689341865999.png
 
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 1
Solution
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
 
Upvote 0

Forum statistics

Threads
1,224,754
Messages
6,180,749
Members
452,996
Latest member
nelsonsix66

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top