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

Closed
RR2105 Posts 2 Registration date Friday May 31, 2013 Status Member Last seen June 3, 2013 - May 31, 2013 at 12:37 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 4, 2013 at 11:06 AM
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 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 1, 2013 at 06:10 PM
Before moving the data, if there is a unique way of identifying that data is already there then it would be possible
0
RR2105 Posts 2 Registration date Friday May 31, 2013 Status Member Last seen June 3, 2013
Jun 3, 2013 at 09:35 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 4, 2013 at 11:06 AM
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
0