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

Report
Posts
2
Registration date
Friday May 31, 2013
Status
Member
Last seen
June 3, 2013
-
Posts
2745
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 17, 2021
-
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

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Before moving the data, if there is a unique way of identifying that data is already there then it would be possible
Posts
2
Registration date
Friday May 31, 2013
Status
Member
Last seen
June 3, 2013

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
Posts
2745
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 17, 2021
461
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!