mykal66
09-20-2023, 06:55 AM
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
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