Moving Data to a new tab when it is complete

[TheKristenDavis]

Hello,
I am having an issue where I need to move a row of data to a completed tab when it is complete.

I want the macro to run each time I change the data in the status column, and when I type complete under a project, to move that project to the "completed" tab.

I tried searching for this code, but the only one I could find assumed that my existing tab was sheet1 and I am using a sheet called "Projects" so it won't work.

My columns are as follows:
<TABLE style="WIDTH: 825pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1099 border=0 x:str><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 262pt; mso-width-source: userset; mso-width-alt: 12763" width=349><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 138pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #969696; mso-ignore: colspan" width=184 colSpan=2 height=21>Project Summary</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" vAlign=top align=left width=151><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:rect id=_x0000_s1037 style="MARGIN-TOP: 5.25pt; Z-INDEX: 10; MARGIN-LEFT: 14.25pt; WIDTH: 60pt; POSITION: absolute; HEIGHT: 13.5pt; mso-wrap-style: tight" o:insetmode="auto" strokecolor="windowText [64]" fillcolor="silver [22]" o:button="t"><v:fill o:detectmouseclick="t"></v:fill><v:textbox style="mso-direction-alt: auto">

Sort​

</v:textbox><?xml:namespace prefix = x ns = "urn:schemas-microsoft-comffice:excel" /><x:ClientData ObjectType="Rect"><x:TextHAlign>Center</x:TextHAlign> </x:ClientData></v:rect><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #969696" width=151 height=21></TD></TR></TBODY></TABLE>
</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 94pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=125></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=70></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=69></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 60pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=80></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=71></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 262pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=349></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #969696" height=17 x:fmla="=NOW()" x:num="40414.598858333331">08/24/2010</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 262pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=349></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #969696" height=17></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl36 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=70>Date</TD><TD class=xl36 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=69>Date</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 60pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=80></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696"></TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 262pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #969696" width=349></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #969696" width=93 height=17>Client/Project</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=91>Plan</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=151>Project Description</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=125>Task Description</TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=70>Received</TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=69>Due</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=80>Responsible</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=71>Status</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 262pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=349>Comments</TD></TR></TBODY></TABLE>
Thanks in advance for help!

 

[lenze]

Hi and welcome to the board!!!
Can you post the code you found?? It's a simple matter to change the sheet references!!!
Also, can you give more detail? Perhaps you can fill in some sample data and the expected result on sheet "Completed" Specifically, what goes in Status column? Where do you type in "Complete"? NEed cell refereces to really help

lenze

 

[TheKristenDavis]

Here is the code I found...

Private Sub Worksheet_Change(ByVal Target As Range) 'this code will run anytime _
there is a change on Sheet1, _
ie this is pasted into the _
Sheet1 object area of the VBE

On Error GoTo ErrorHandler 'on error, the error handler will be invoked below

If Target.Column = 2 And Target.Value = "Yes" Then 'This will check whether the changed _
cell is in column 2, and now has the value of "Yes", and if so, will run the following code _
if not, will exit the sub

Dim DestRow As Range 'Not necessary, but Dim the destination row as a range

rngToCut = Target.Address 'give the value of the cell which was just changed to a _
"Yes", to rngToCut (this could be any word etc)

Set DestRow = Worksheets("Sheet2").Range("A" & CStr(Application.Rows.Count)). _
End(xlUp).Offset(1, 0) 'find the first blank row in column 1 of Sheet2 by _
working it's way up column A to the first cell with text, and then moves one cell down. _
The name DestRow is then given the row value of this cell

Target.EntireRow.Cut Destination:=DestRow 'cut the row that was changed on Sheet1 _
& insert it into the row on Sheet2 that _
is blank (now called DestRow)

Application.CutCopyMode = False 'remove the marquee (the box around the pasted range)

Worksheets("Sheet1").Range(rngToCut).EntireRow.Delete Shift:=xlUp 'deletes the row _
in sheet 1 where the 'Yes' value came from. Note the range of rngToCut which identifies _
the cell that was changed, and now selects that cells entire row, and deletes it
End If
Exit Sub

ErrorHandler: 'the error handler clears any errors, and ends the sub
Err.Clear

End Sub

Here's an example of what I would have in the rows, I will change the status to in-progress, started, waiting on someone else, etc then finally to complete when everything is done.

<TABLE style="WIDTH: 825pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1099 border=0 x:str><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 262pt; mso-width-source: userset; mso-width-alt: 12763" width=349><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #969696" width=93 height=17>Client/Project</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=91>Plan</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=151>Project Description</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=125>Task Description</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=70>Received</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=69>Due</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=80>Responsible</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=71>Status</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 262pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696" width=349>Comments</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=93 height=17>ABC Client</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=91>ABC Plan</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=151>Update Data</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=125>Change Salary Codes</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=70 x:num="40414">08/24/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=69 x:num="40421">08/31/2010</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>Kristen</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71>In-Progress</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 262pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=349> </TD></TR></TBODY></TABLE>

Let me know if there is anything else I can provide. Thank you for your help!

 

[Smitty]

Welcome to the Board!

This should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("H:H")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            If LCase(Target.Value) = "complete" Then
                With Target.EntireRow
                    .Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                    .Delete
                End With
            End If
End Sub

HTH,

 

[TheKristenDavis]

I get the following error:

subscript out of range

When I change the status to completed.

The VBE highlights:

.Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Off

 

[lenze]

.Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1)

lenze <!-- / message --><!-- sig -->

 

[TheKristenDavis]

That's what I had, sorry it cut off when I was copying it in the post. It still won't work.
Any ideas?

 

[TheKristenDavis]

Nevermind, I had a blonde moment - it works! Thanks!