Action list - Transfer to - Completed List. Paste Special (Values)

jilbobagins

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
 
Please wrap your code with code tags ( the #button). Thanks
 
Code: 
 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
 
How about
Code: 
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
       [COLOR=#ff0000] Sheets("Completed").Range("B" & LrowCompleted + 1).Resize(, 13).Value = Range("B" & Target.Row & ":N" & Target.Row).Value[/COLOR]
        'Delete Row from Project List
        Range("B" & Target.Row & ":N" & Target.Row).Delete xlShiftUp
    End If
    Application.EnableEvents = True
End Sub
 
You're welcome & thanks for the feedback
 
You must log in or register to reply here.
Back
Top