Moving a line from one tab to another based on cell value

O

okeefec93

New member
Joined
Jan 7, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
When entering "Yes" into the last column i want this to then move onto the second tab using the same format, so on the first tab i have details of uncompleted changes and on the second details on the completed changes. I also need to be able to filter the columns without it upsetting the code?

Can anyone help please?View attachment Training material update log.xlsm
 
Try this in the code-module of the sheet called Awaiting sign off:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
If Not Intersect(Target, Columns("K")) Is Nothing Then
  If Target.Cells.Count = 1 Then
    If LCase(Target.Value) = "yes" Then
      With Sheets("Signed off")
        Set Destn = .Cells(.Rows.Count, "A").End(xlUp).Offset(1) 'the success of this line ddepends on there always being something in column A of the destination sheet.
        Application.EnableEvents = False
        With Cells(Target.Row, "A").Resize(, 11)
          .Copy Destn
          .Delete Shift:=xlUp
          '.EntireRow.Delete 'alternative to line above.
          Application.EnableEvents = True
        End With
      End With
    End If
  End If
End If
errhandler:
Application.EnableEvents = True
End Sub
It will immediately move the line to the Signed off sheet on entering Yes in column K.
It will only work if one cell is changed (not multiple cells at once).
 
You must log in or register to reply here.
Back
Top