title: copy part of a row to another sheet unless already been copied. Source rows can change row
body: <p>I basically need to copy rows from one sheet to another. The rows which I need to copy are on sheet 2 , columns A to N and I need to copy the rows that has the unique value in column N. </p>
  
  <p>Column N will just be <code>=M1 or =M2</code> depending on which row has data on it. </p>
  
  <p>Hence I will use a worksheet calculate event to try capture this. </p>
  
  <p>The tricky part is that each time new values exist in say the last row on sheet 2 and then N gets filled form M. I don't want the previously copied rows to be copied. It may also be the case that the entire contents of the rows change places or that one of the rows disappears and the other row will fill its gap on sheet 2. So I need to avoid the calculate event recognizing this from the <code>=M</code> line. i.e if it has already been copied but gets recalcuated - -I don't need it to be copied again. </p>
  
  <p><strong>I guess one way to do this would be toi lookup if the N column value exists in the N column value on sheet 1. Because if that row disappears then it will be on the sheet 1. it will because I have other formulas putting it there.</strong> </p>
  
  <p>My starting point for dong this is the code below and I have this set in the worksheet code of sheet 1</p>
  
  <pre><code>Private Sub Worksheet_Calculate()
  Dim i As Long
  Dim lr1 As Long, lr2 As Long
  Dim Delta As String
  Dim wks1 As Worksheet, wks2 As Worksheet
  
  Set wks1 = ActiveSheet
  Set wks2 = Worksheets("Sheet2") 'change to suit
  lr1 = wks1.Cells(Rows.Count, "N").End(xlUp).Row
  
  For i = 2 To lr1
  
          lr2 = wks2.Cells(Rows.Count, "A").End(xlUp).Row + 1
          wks1.Cells(i, "N").EntireRow.Copy Destination:=wks2.Cells(lr2, "A")
  
  Next i
  End Sub
  </code></pre>
  
  <p>I also have this working which I would need to incorporate into that worksheet calcualte </p>
  
  <pre><code>Sub updt()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
  Set sh1 = Sheets(1) 'Edit sheet name
  Set sh2 = Sheets(2) 'Edit sheet name
  lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
  Set rng = sh1.Range("A2:A" &amp; lr)
  For Each c In rng
  If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
  sh2.Range("A" &amp; sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2) = c.Value
  End If
  Next
  End Sub
  </code></pre>