Pasting Values using "Private Sub Worksheet_Change(ByVal Target As Range)"

[samuerle]

Hey all,
Very novice when it comes to VBA, but I'm pretty good at copy/paste and brute forcing until something works! I managed to come up with this code that allows multiple values to be selected from drop downs (target.column section) as well as copying and pasting a row from an "Open" tab to a "COMPLETED" tab once the completed? field (column 22) is marked "Yes".

The issue is when it moves the row to the COMPLETED tab, it pastes formulas, which destroys one column. Looking online, I thought the answer would be to add .PasteSpecial xlPasteValue to the end of line 11 - Range("A" & Target.Row &":W" & Target.Row).CopySheets("Completed").Range("A" & LrowCompleted + 1)
but alas this does not work. I'm open to any ideas to solve this, including hidden columns and whatnot. Thanks so much in advance!!!

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error ResumeNext
Dim Oldvalue As String
Dim Newvalue As String
   Application.EnableEvents = False
    'If Cell that isedited is in column U and the value is completed then
    If Target.Column =22 And Target.Value = "Yes " Then
        'Define lastrow on completed worksheet to know where to place the row of data
        LrowCompleted= Sheets("COMPLETED").Cells(Rows.Count, "A").End(xlUp).Row
        'Copy andpaste data
        Range("A" &Target.Row & ":W" & Target.Row).CopySheets("Completed").Range("A" & LrowCompleted + 1)
        'Delete Rowfrom Project List
       Range("A" & Target.Row & ":W" &Target.Row).Delete xlShiftUp
    End If
   Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Or Target.Column = 3 Or Target.Column =4 Or Target.Column = 7 Or Target.Column = 10 Or Target.Column = 11 OrTarget.Column = 12 Or Target.Column = 13 Or Target.Column = 14 Or Target.Column= 15 Or Target.Column = 18 Or Target.Column = 19 Then
  IfTarget.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: IfTarget.Value = "" Then GoTo Exitsub Else
   Application.EnableEvents = False
    Newvalue =Target.Value
    Application.Undo
    Oldvalue =Target.Value
      If Oldvalue ="" Then
        Target.Value =Newvalue
      Else
        If InStr(1,Oldvalue, Newvalue) = 0 Then
           Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value =Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

End Sub

 

[samuerle]

I'm really lucky I'm married to a software engineer! I was close - apparently ".copy" and "sheets" needed to be on separate lines!
Here's the final, working code.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
Dim Oldvalue As String
Dim Newvalue As String
    Application.EnableEvents = False
    'If Cell that is edited is in column U and the value is completed then
    If Target.Column = 22 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, "A").End(xlUp).Row
        'Copy and paste data
        Range("A" & Target.Row & ":W" & Target.Row).Copy
        Sheets("Completed").Range("A" & LrowCompleted + 1).PasteSpecial (xlPasteValues)
        'Delete Row from Project List
        Range("A" & Target.Row & ":W" & Target.Row).Delete xlShiftUp
    End If
    Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Or Target.Column = 3 Or Target.Column = 4 Or Target.Column = 7 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 12 Or Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 18 Or Target.Column = 19 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True




End Sub