Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete the copy of row to another worksheet when date is deleted
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete the copy of row to another worksheet when date is deleted
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 "Peaches" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot Move, Copy, Insert, Rename, or Delete a Worksheet in Any Wo | Excel Discussion (Misc queries) | |||
how do i delete filename when file already deleted? | Excel Discussion (Misc queries) | |||
How do I delete checkboxes from rows I deleted in a macro? | Excel Discussion (Misc queries) | |||
macro to copy and edit then delete a worksheet | Excel Discussion (Misc queries) | |||
Copy Cell value from one worksheet to another, then delete. | Excel Programming |