Moving rows when cell value is changed

[mykal66]

Hi

Can anyone help with this piece of code please. I want an entire row to move to a separate worksheet in the same work book when a cell value is marked as completed (the sheet it moves to is called completed too). The code does work but; when i click into the cell i get a runtime error before making any changes and i just need it to do nothing unless "COMPLETED" is selected from a drop down list in column 6

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Moves completed cases
Dim lngNextRow As Long
    If Target.Cells.Count > 1 Then Exit Sub
    'Change column number as required
    If Target.Column = 6 And Target.Row > 1 Then
        'This looks for first empty cell in column A, amend as required
        lngNextRow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Target.EntireRow.Copy Sheets(Target.Value).Range("A" & lngNextRow)
        Target.EntireRow.Delete xlUp
End If
End Sub

[georgiboy]

Maybe change the SelectionChange to just Change, also add another AND to your If:

Private Sub Worksheet_Change(ByVal Target As Range) 'Moves completed cases
    Dim lngNextRow As Long
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    'Change column number as required
    If Target.Column = 6 And Target.Row > 1 And Target.Value = "Completed" Then
        'This looks for first empty cell in column A, amend as required
        lngNextRow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Target.EntireRow.Copy Sheets(Target.Value).Range("A" & lngNextRow)
        Target.EntireRow.Delete xlUp
    End If
End Sub