Transfering data from one sheet to another, but not duplicating. Closed

Question

Good day all. Checking to see if anyone could help out, as iam very new to the excel world.

I have a sheet that I am transferring data to another sheet with.  I have it set up that when column P says "Text" I hit the transfer button and it transfers the data from that row over to another sheet called "LOG".  The problem is that whenever I hit the transfer button it keeps duplicating the rows even though they are already on the log sheet.  I was wondering if there is a way for it to recognize that the data is already on the log sheet and not duplicate it.  I have timestamps set up on the first sheet so I can see when someting changes.

   Application.ScreenUpdating = False
If Not Intersect(Target, Range("P:P")) Is Nothing Then
    If Target.Value = "WELL DOWN" Then
        Range("B" & Target.Row, "BH" & Target.Row).Copy
        Sheets("LOG").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
This is what Iam using right now.  Any help would be appreciated.  Hopefully I have expained well enough as to what Iam trying to do.

Thanks in advance for any help
RR2105

rizvisa1 · Answer

Before moving the data, if there is a unique way of identifying that data is already there then it would be possible

RR2105 · Answer

I have a timestamp set up in column Q that puts a date and time in when column P changes to welldown.  The timestamp does not change until the status changes in column P back to running.  Then the timestamp disappears.  I was hoping to use the timestamp in some way for it to recognize that the date and time had not changed and not copy it to the log sheet, but if it has changed then copy it.  Any ideas on how to get that to work?  Thanks
RR2105

TrowaD · Answer

Hi RR2105,

Little confused.

You talk about hitting a button to activate the code, but your code is set up to run automatically (ie. Worksheet_Change). 

You talk about the code reacting to the text "Text" but in the code you refer to "WELL DOWN".

I will go with your story.

Try this code:
Sub test()
Dim Target As Range
Dim Exist As Boolean
Dim lRow As Integer

Application.ScreenUpdating = False

Set Target = ActiveCell
Exist = False
lRow = Sheets("LOG").Range("P" & Rows.Count).End(xlUp).Row

If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub

For Each cell In Sheets("LOG").Range("P1:P" & lRow)
If cell.Value = Target.Offset(0, 1).Value Then Exist = True
Next cell

If Target.Value = "Text" And Exist = False Then
Range("B" & Target.Row, "BH" & Target.Row).Copy
Sheets("LOG").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
Best regards,
Trowa

Transfering data from one sheet to another, but not duplicating.

3 responses

Similar discussions

Subscribe To Our Newsletter!