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