Using paste special to copy rows into new sheet VBA

[Georgia101]

Hello,

I am new to VBA and have successfully used the below code to copy a row based on unique identifier ,column 15 as I have multiple duplicate data. The row is copied into another worksheet “flag list” at the bottom based on having Y in the unique column.

However I cannot figure out how to modify to ensure it is paste special, so that when I update the original rows in the workbook the copied rows on flag list autoupdate to reflect changes.

I also have a part to my code so that when a row is changed to N it deleted this row from the flag list.

Any help at all with modifying to paste linkcells (paste special) would be much appreciated.

Many thanks,

Georgia

the code :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)



If Target.Column = 12 Then

Set destinationSheet = ThisWorkbook.Sheets("FLAG LIST")

uniqueColumnlookup = 15

vNew = Target.Value

Application.EnableEvents = False

Application.Undo

vOld = Target.Value

Target.Value = vNew

Application.EnableEvents = True

If vNew <> vOld Then

lastrow = destinationSheet.Range("A" & Rows.Count).End(xlUp).Row

If vNew = "Y" Or vNew = "y" Then

lastrow = destinationSheet.Range("A" & Rows.Count).End(xlUp).Row

Sh.Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=destinationSheet.Cells(lastrow + 1, 1)

End If

If vNew = "N" Or vNew = "n" Then

For i = 1 To lastrow

If destinationSheet.Cells(i, uniqueColumnlookup).Value = Sh.Cells(Target.Row, uniqueColumnlookup).Value Then

destinationSheet.Rows(i).EntireRow.Delete

End If

Next

End If

End If



End If

End Sub

 

[MARK858]

Maybe..

    If LCase(vNew) = "y" Then

        lastrow = destinationSheet.Range("A" & Rows.Count).End(xlUp).Row

        Sh.Cells(Target.Row, Target.Column).EntireRow.Copy
        destinationSheet.Cells(lastrow + 1, 1).PasteSpecial xlValues
        Application.CutCopyMode = False
    End If