Moving Row from Active Workbook to Complete Workbook

[SheboyganFalls9]

I am building a workbook that when a cell is filled it will move that row from the active workbook to the complete workbook. The rows will be random about which one is moving at a time so I would like the Complete moving to be the next available row. How would I accomplish this?

 

[dreid1011]

What is the trigger to determine if a row is complete? And do you want to move the data from one sheet to another and remove the data from the original sheet, or just copy to new sheet?

 

[SheboyganFalls9]

What is the trigger to determine if a row is complete? And do you want to move the data from one sheet to another and remove the data from the original sheet, or just copy to new sheet?

The trigger would be the complete column cell. I would like the removed completely from the active tab and moved to the complete tav

 

[dreid1011]

What value would be entered into the Complete column cell?

 

[SheboyganFalls9]

What value would be entered into the Complete column cell?

the word Yes

 

[dreid1011]

Which column will always have data in it when new entries are created?

 

[SheboyganFalls9]

Which column will always have data in it when new entries are created?

All of the columns will be filled out from A to P. I added the Q column so I could make that the trigger spot for when the row should be moved from active to completed.

 

[dreid1011]

Okay, I believe the following code will do what you're after, but I had to stop testing it as my Excel started behaving erratically while using it. So... test it on a copy of your book, and I hope it's just my Excel being stupid.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SetMeFree
Dim ws As Worksheet
Dim lRowA As Long, lRowC As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

Set ws = Worksheets("Complete")
lRowA = Range("A" & Rows.Count).End(xlUp).Row
lRowC = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

If Not Intersect(Target, Range("Q2:Q" & lRowA)) Is Nothing And Target.Count = 1 Then
    If Range("Q" & Target.Row).Value = "Yes" Then
        Range("A" & Target.Row & ":Q" & Target.Row).Copy
        ws.Range("A" & lRowC).PasteSpecial xlPasteValues
        Rows(Target.Row & ":" & Target.Row).Delete Shift:=xlUp
        Application.CutCopyMode = False
    End If
Else
   GoTo SetMeFree
End If

SetMeFree:
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[SheboyganFalls9]

Okay, I believe the following code will do what you're after, but I had to stop testing it as my Excel started behaving erratically while using it. So... test it on a copy of your book, and I hope it's just my Excel being stupid.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SetMeFree
Dim ws As Worksheet
Dim lRowA As Long, lRowC As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

Set ws = Worksheets("Complete")
lRowA = Range("A" & Rows.Count).End(xlUp).Row
lRowC = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

If Not Intersect(Target, Range("Q2:Q" & lRowA)) Is Nothing And Target.Count = 1 Then
    If Range("Q" & Target.Row).Value = "Yes" Then
        Range("A" & Target.Row & ":Q" & Target.Row).Copy
        ws.Range("A" & lRowC).PasteSpecial xlPasteValues
        Rows(Target.Row & ":" & Target.Row).Delete Shift:=xlUp
        Application.CutCopyMode = False
    End If
Else
   GoTo SetMeFree
End If

SetMeFree:
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

It works perfectly. Thank you so much for your help

 

[dreid1011]

You're welcome, and happy to help.