Solved Move Row to Another Tab if Complete in Excel 2010

May 11, 2016 at 11:19:20
Specs: Windows 7
I have a worksheet with two tabs, a "Contracts" tab and a "Completed" tab. The contracts tab has a contract in each row, and in column Q is the "Complete?" column. When contracts are completed, you type "Complete" into the cell in column Q (or select it from the drop down I created with Verification).

Once "Complete" is entered into the cell in column Q, I would like it to move to the "Complete" tab and have every row below it in the "Contracts" tab to move up.

This worksheet will have contracts added to the bottom of it fairly frequently. Contracts are not completed in any particular pattern, it is just whenever all the signatures come in so there is some volatility here.

Any and all help is appreciated.


See More: Move Row to Another Tab if Complete in Excel 2010

Report •


#1
May 11, 2016 at 11:44:12
✔ Best Answer
Right click the sheet tab for Contracts and choose View Code. Paste the following code into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 17 Then
  If Target = "Complete" Then
   nxtRw = Sheets("Complete").Range("A" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy Sheets("Complete").Range("A" & nxtRw)
    Target.EntireRow.Delete shift:=xlUp
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 11, 2016 at 12:13:06
YES! Thank you! No immediately apparent hang ups in the process. Seems to work perfectly.

Report •

#3
September 23, 2016 at 10:22:04
I'm using the code provided above, modified to suit my worksheet but I keep receiving Run Time Error 13. The code works and is doing what is expected but how can I clear this error? It's directing me to debug If Target = "Complete" Then

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 1 Then
  If Target = "Complete" Then
   nxtRw = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy Sheets("Completed").Range("A" & nxtRw)
    Target.EntireRow.Delete shift:=xlUp
  End If
 End If
End Sub


Report •

Related Solutions

#4
September 23, 2016 at 12:34:38
This was an interesting one! It took me a while to figure it out and it was something I had not encountered before. Allow me to explain what is happening. Some of this you may already know, but I'm going to explain it all just for clarity.

Let's start with some background related to how VBA deals with the Address of a Range. Obviously, when a single cell is selected, the address is the address of the single cell.

e.g. If you select H5 run this code, you will see $H$5

Sub Test
 MsgBox Selection.Address
End Sub

If you run this code, you will see 8, which is the column number for Column H:

Sub Test
 MsgBox Selection.Column
End Sub

Ok, so now select H5:K10 and run each little macro. The Address will now be $H$5:$K$10 - the address of the entire range - but the Column will still be 8. That is because VBA considers the cell in the upper left of the range to be Cell(1) of the range and returns that column number.

Now, let's see what is happening with the Wroksheet_Change macros and why you are getting an error...

A Worksheet_Change macro fires at every change to the sheet. In this case, the first instruction checks to see if the change was made in the designated column. In the original macro, it checks to see if the change was made to Column 17. If Yes, then the rest of the code runs. If not, the macro exits.

If Target.Column = 17 Then

Now a key point here is that the following instruction is also a change to the sheet:

Target.EntireRow.Delete shift:=xlUp

Therefore, even though you don't see it, the code actually fires again when the row is deleted. That deletion creates a new "Target" - the entire row that was deleted. For example, if Row 11 was deleted, the address of the Target would be $11:$11.

Now remember the Column number of a Range? It's the first Column of that Range. Therefore, if the Range is an entire row, as in the case of our new Target, the column number of the Target is 1.

So in the original macro, when the code fires again (at the deletion of the row) and checks to see if the Target.Column is 17, the answer is "No" and the code exits.

Now, let's look at your version, where you are checking for Column 1:

If Target.Column = 1 Then

Here's what is happening:

As mentioned before, when the Target row is deleted, the code fires again. Internally, the address of the Change is the entire row and VBA sees the Target.Column as 1. (the first column in the changed range). Since your code is looking for a change to Column 1, the folowing instruction is True and the code continues with its next instruction:

If Target.Column = 1 Then

What's the next instruction? It's...

If Target = "Complete" Then

You are asking VBA if an entire Row contains "Complete". I don't mean that you are asking VBA if every cell in the Row contains "Complete", I mean you are asking VBA if the row itself contains "Complete". Since that can't happen, you end up with a Type Mismatch error.

OK, so after all that, how do we fix it? It's simple! We just don't let the Change code fire again when the row is deleted. If we disable Events before we delete the row and then enable them again afterwards, the deletion won't cause the Change code to fire at the deletion, but it will ready to fire at the next "user initiated" change to the worksheet.

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 1 Then
  If Target = "Complete" Then
   nxtRw = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy Sheets("Completed").Range("A" & nxtRw)
     Application.EnableEvents = False
      Target.EntireRow.Delete shift:=xlUp
     Application.EnableEvents = True
  End If
 End If
End Sub

The only thing to be aware of is that if you stop the code (or it errors out on its own) after the Application.EnableEvents = False instruction executes and before Application.EnableEvents = True executes, the code will not fire ever again – unless you EnableEvents by running a real short macro:

Sub FixEvents()
  Application.EnableEvents = True
End Sub

This is because Application.EnableEvents = False is an Application level instruction which will prevent all Event code from running in any workbook that is currently open.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question