jilbobagins
Member
- Joined
- Apr 11, 2019
- Messages
- 80
- Reaction score
- 0
- Points
- 6
- Excel Version(s)
- 2016
Hi,
Wondering if anyone can help.
I have an Action list which when the action is complete (Column K = YES) it copies the line of data into the Completed worksheet.
Now the only issue is I have formulas in some columns where I need the Values pasting across and not the formula.
Im no expert in these matters!! And found this VBA elsewhere and it work well apart from this bit...
Many Thanks in advance!
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
'If Cell that is edited is in column U and the value is completed then
If Target.Column = 11 And Target.Value = "YES" Then
'Define last row on completed worksheet to know where to place the row of data
LrowCompleted = Sheets("Completed").Cells(Rows.Count, "B").End(xlUp).Row
'Copy and paste data
Range("B" & Target.Row & ":N" & Target.Row).Copy Sheets("Completed").Range("B" & LrowCompleted + 1)
'Delete Row from Project List
Range("B" & Target.Row & ":N" & Target.Row).Delete xlShiftUp
End If
Application.EnableEvents = True
End Sub
Wondering if anyone can help.
I have an Action list which when the action is complete (Column K = YES) it copies the line of data into the Completed worksheet.
Now the only issue is I have formulas in some columns where I need the Values pasting across and not the formula.
Im no expert in these matters!! And found this VBA elsewhere and it work well apart from this bit...
Many Thanks in advance!
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
'If Cell that is edited is in column U and the value is completed then
If Target.Column = 11 And Target.Value = "YES" Then
'Define last row on completed worksheet to know where to place the row of data
LrowCompleted = Sheets("Completed").Cells(Rows.Count, "B").End(xlUp).Row
'Copy and paste data
Range("B" & Target.Row & ":N" & Target.Row).Copy Sheets("Completed").Range("B" & LrowCompleted + 1)
'Delete Row from Project List
Range("B" & Target.Row & ":N" & Target.Row).Delete xlShiftUp
End If
Application.EnableEvents = True
End Sub