Automatically Move Row to Another Sheet in excel

[rabia01]

Hi everyone,

I have an excel spreadsheet with a table on 2 sheets.

One for items to be ordered and the second for items that have been ordered.
I want to know if on the 'to be ordered' tab once its marked as complete can it automatically be moved to the 'completed' tab?
I know the basics of excel so I have tried to read previous threads but really wasn't sure.

Thank you for your help

 

[Crystalyzer]

give this a try on a copy of your workbook.

Assumptions:

1. you are using tables and not simply ranges
2. There is a column in the table that a user can enter a value to indicate the order is completed. I used column 6 of the table. you will have to adjust this to your needs as indicated by the comment in the code.

Private Sub Worksheet_Change(ByVal t As Range)
    Dim comSh As Worksheet
    Dim ordSh As Worksheet
    Dim loRow As Long
    Dim nxtRow As Long
    Dim tRow As Long
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set comSh = ThisWorkbook.Sheets("Completed")
    Set ordSh = ThisWorkbook.Sheets("TB Ordered")
    
    tRow = t.Row
    
    On Error Resume Next
    If t.Column = 6 And (t.Value <> 0 Or t.Value <> "") Then 'Change this to the corresponding column number for completed items
        On Error GoTo 0
        Range(Cells(t.Row, 1), Cells(t.Row, t.Column)).Cut
        
        With comSh
            nxtRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
            .Activate
            .Range(.Cells(nxtRow, 1), .Cells(nxtRow, t.Column)).Select
            .Paste
            .Cells(1, 1).Select
        End With
        
        With ordSh
            .Activate
            loRow = tRow - .Cells(tRow, 6).ListObject.DataBodyRange.Row + 1
            .Cells(tRow, 6).ListObject.ListRows(loRow).Delete
            .Cells(1, 1).Select
        End With
    End If
    On Error GoTo 0
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub