delete the copy of row to another worksheet when date is deleted

[Peaches]

I thank Nigel for helping with the code to copy cells to another
worksheet when a date is added. I tried doing another code to delete
the copy if the date is deleted, but I am new and not sure what all the
codes mean yet or where to put the false statement or if this is
possible. Everything I tried does not work. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'from Google Group microsoft.public.excel.programming
If Target.Column = 4 And Target.Row > 1 Then
If IsDate(Target.Value) And Cells(Target.Row, 30) <> 1 Then
'transfer parts of this row to Record sheet or delete row if
column date is deleted
With Sheets("Record")
' <<< change the name of the target sheet here

'determine last row on record sheet
Dim xlr As Long
xlr = .Cells(.Rows.Count, 1).End(xlUp).Row

'copy selected cells to record from production (change for
other columns ect,)
.Cells(xlr + 1, 1) = Cells(Target.Row, "D")
.Cells(xlr + 1, 2) = Cells(Target.Row, "F")
.Cells(xlr + 1, 3) = Cells(Target.Row, "H")
.Cells(xlr + 1, 4) = Cells(Target.Row, "N")
.Cells(xlr + 1, 5) = Cells(Target.Row, "T")
.Cells(xlr + 1, 6) = Cells(Target.Row, "U")

'record that row data has been transfered
Cells(Target.Row, 30) = 1

'sort the record sheet by PC
.Range("A2:F" & xlr + 1).Sort Key1:=.Range("D2"),
Order1:=xlAscending



End With
End If
End If
End Sub


Thank you for any help,
Peaches

 

[Greg Wilson]

According to my read of your situation, the following code will work if you
DON'T SORT after copying the data to Sheets("Record"). If you sort after the
copy then there is no longer a simple positional relationship between the
transfered data and the source row.

I think you will need to use some form of search code (either the Find
method or loop) to find the destination row in sheet Record after deleting
the date in the source row. However, there will need to be a unique
identifier common to both source row and destination row for this to work.
For example, if there can only be one entry made with the same date in the
source sheet then the search code would just look for this date in the
destination sheet (Record) and always find the correct record. If there can
be more than one entry made with the same date and there is nothing else
unique to the entry in the source row then I think you will have to create
one (i.e. a tag) that is copied as well.

I suggest that you run the following and confirm/deny if it works with the
sort code turned off. If it works then someone should be able to complete it
once the unique identifier issue is resolved.

Hope I'm not confused again as usual.

Regards,
Greg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, xlr As Long
Dim RecordCell As Range
Dim Arr As Variant

If Target.Count > 1 Then Exit Sub
If Target.Column = 4 And Target.Row > 1 Then
Arr = Array(4, 6, 8, 14, 20, 21)
Set RecordCell = Cells(Target.Row, 30)
With Sheets("Record")
xlr = .Cells(.Rows.Count, 1).End(xlUp).Row
If IsDate(Target.Value) And RecordCell <> 1 Then
For i = 1 To 6
.Cells(xlr + 1, i) = Cells(Target.Row, Arr(i - 1))
Next
RecordCell = 1
'.Range("A2:F" & xlr + 1).Sort Key1:=.Range("D2"), _
Order1:=xlAscending
ElseIf Target = "" And RecordCell = 1 Then
For i = 1 To 6
.Cells(xlr, i).ClearContents
Next
RecordCell.ClearContents
End If
End With
End If
End Sub