Help With VBA to Move one Row on one Table to Another Table

[Mista_sav]

I have identical Tables, one is called Active and one is called Completed. They are on separate Sheets, also called Active and Completed.

In column H there is an option to select the status. When someone selects "Complete - No Further Action Required" i would like it to move (then delete) this row from the table and add it to the Completed table.

Any help on a VBA to do this automatically?

 

[Mista_sav]

Sorry mate I ended up seeing an issue.

Example: I would put in 4 rows of data but lets say row 4 is completed, it would move row 2 to completed and not the row i selected if that makes sense. So it was basically moving the first row over to the new sheet when i was changing the 4th row

 

[kevin9999]

I figured we weren't finished with this as you hadn't marked my answer as a Solution. Both sets of code I provided uses a filter to include all instances of "Complete - No Further Action Required" to the Completed sheet. If it isn't moving all instances then there's not a lot I can do without seeing your actual Active sheet using the XL2BB - Excel Range to BBCode, or better still, share a copy of your workbook via Dropbox, Google Drive or similar file sharing site.

 

[Mista_sav]

I figured we weren't finished with this as you hadn't marked my answer as a Solution. Both sets of code I provided uses a filter to include all instances of "Complete - No Further Action Required" to the Completed sheet. If it isn't moving all instances then there's not a lot I can do without seeing your actual Active sheet using the XL2BB - Excel Range to BBCode, or better still, share a copy of your workbook via Dropbox, Google Drive or similar file sharing site.

Sorry if i didnt explain it properly; what i need it to do is only move the row im changing. I think this is xl2bb? Let me know if it doesnt work

 

[Mista_sav]

Settlement Tracking.xlsm

drive.google.com

does this work mate?

 

[Mista_sav]

Settlement Tracking.xlsm

drive.google.com

does this work mate?

apologies this will be it Settlement Tracking.xlsm

 

[kevin9999]

what i need it to do is only move the row im changing.

Yes, that's very different. Thanks you for sharing your file - makes life much easier. Leave it with me.

 

[kevin9999]

It looks like the Target column has shifted from H to O. That being the case, please try the following code - put on the sheet code area for the sheet Active.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("O2:O" & Cells(Rows.Count, "O").End(xlUp).Row), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set ws1 = Worksheets("Active")
        Set ws2 = Worksheets("Completed")
            
        If Target = "Complete - No Further Action Required" Then
            Target.EntireRow.Copy
            ws2.Range("A2").Insert xlShiftDown
            Application.CutCopyMode = False
            Target.EntireRow.Delete
        End If
        
    End If
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[Mista_sav]

It looks like the Target column has shifted from H to O. That being the case, please try the following code - put on the sheet code area for the sheet Active.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("O2:O" & Cells(Rows.Count, "O").End(xlUp).Row), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set ws1 = Worksheets("Active")
        Set ws2 = Worksheets("Completed")
           
        If Target = "Complete - No Further Action Required" Then
            Target.EntireRow.Copy
            ws2.Range("A2").Insert xlShiftDown
            Application.CutCopyMode = False
            Target.EntireRow.Delete
        End If
       
    End If
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Yeah mate sorry i added some rows, i thought i changed the Code to reflect that, but then when they started using it the issue started happening where if someone on row 4 completed it; it then moved row 1.

Is this code different otherwise?? So far it works pretty sweet!!!

 

[kevin9999]

Is this code different otherwise?

Yes, doesn't use a filter to copy all the rows with "Complete - No Further Action Required" in the target column - just the row that you have just changed.

 

[Mista_sav]

Yes, doesn't use a filter to copy all the rows with "Complete - No Further Action Required" in the target column - just the row that you have just changed.

champion, tested properly this time and works to perfection! i have marked the above as successful i think?

Cheers again mate, muchly appreciated