+ Reply to Thread
Results 1 to 8 of 8

Copy + Paste Only New Unique Rows

Hybrid View

  1. #1
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Copy + Paste Only New Unique Rows

    The attached code works well on what it is intended to do.

    Rows enter the Incoming sheet via a web query. Then these rows are sorted by removing duplicates and move to Checked sheet. This is where help is needed.

    As Incoming sheet will be updated every 30 minutes or so, eventually after the first, second or even third refresh, the same rows are going to be moved to Checked Sheet again and again.

    Is there a way to prevent this from happening.

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy + Paste Only New Unique Rows

    See if this code will do what you wish

    Sub ColorCheck()
    Dim r As Long, i As Long
    Dim Lastrow, lr1 As Long, lr2 As Long
    Dim rs1 As Worksheet, rs2 As Worksheet, rs3 As Worksheet
    Dim y As Date
    Dim dict As Object
    
    Set dict = CreateObject("Scripting.Dictionary")
    y = Now
    Set rs1 = Sheets("Incoming")
    Set rs2 = Sheets("Checked")
    Set rs3 = Sheets("Archived")
    
    ActiveWorkbook.RefreshAll
    Application.ScreenUpdating = False
    
    Lastrow = rs1.Range("A" & Rows.Count).End(xlUp).Row '
    
        rs1.Range("$A$1:$B" & Lastrow).RemoveDuplicates Columns:=1, Header:= _
            xlYes
    
    lr1 = rs1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = rs2.Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lr2
        If Not dict.exists(rs2.Cells(i, 1).Value) Then
            dict.Add rs2.Cells(i, 1).Value, i
        End If
    Next i
    
    For r = 1 To lr2
    
    If rs2.Cells(r, "B") < y Then
        rs2.Cells(r, "A").EntireRow.Copy Destination:=rs3.Range("A" & Rows.Count).End(xlUp).Offset(1)
        rs2.Cells(r, "A").EntireRow.Delete
        
    End If
    
    Next r
    
    For r = 1 To lr1
            If (rs1.Cells(r, "A") = "Green" Or rs1.Cells(r, "A") = "Red") And Not dict.exists(rs1.Cells(r, "A").Value) Then
                rs1.Cells(r, "A").EntireRow.Copy Destination:=rs2.Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
    Next r
    
    Application.ScreenUpdating = True
    
    End Sub

  3. #3
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Copy + Paste Only New Unique Rows

    Maniacb thank you for your time.

    Close enough but this is taking into account only the color and not the date. So if we have lets say a Red for 10/5 which will successfully go to Checked sheet, any other Red will fail to be moved.

    I have updated the code so removing duplicates should take into account both color and date

    rs1.Range("$A$1:$B" & Lastrow).RemoveDuplicates Columns:=Array(1, 2), Header:= _
            xlYes
    Last edited by whitoulias; 05-07-2021 at 05:39 AM.

  4. #4
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,957

    Re: Copy + Paste Only New Unique Rows

    So why not just overwrite the Checked sheet all the time...
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  5. #5
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Copy + Paste Only New Unique Rows

    Because i am afraid that if i overwrite that sheet, then the checks made by those two people will be mixed up.

  6. #6
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,957

    Re: Copy + Paste Only New Unique Rows

    Why not explain step by step what you are wanting...Your remove duplicates snippet is not gonna work...

    So You are wanting code to check every entry in Incoming...
    If cells in Col A & B are same then only pass one of the same to Complete
    If already exists in Complete...Don't pass...?

  7. #7
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Copy + Paste Only New Unique Rows

    That is correct.
    So the Incoming sheet will be updated every like 30 minutes through a web query.
    Duplicates based on color and time will be removed and only one row will go to Checked. If that specific combination (color + time) already exists in Checked it should not be transferred .
    Then two people will check that entry and once the time elapses row will be moved to Archived.
    Thank you

  8. #8
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,957

    Re: Copy + Paste Only New Unique Rows

    Sub J3v16()
    Dim Data, Temp, i As Long, ii As Long
    With Sheet1.Cells(1).CurrentRegion
        .RemoveDuplicates Array(1, 2), xlYes
        Data = .Value: ReDim Temp(1 To UBound(Data), 1 To 2)
        For i = 2 To UBound(Data)
            With Sheets("Checked")
                lr = .Cells(.Rows.Count, 1).End(xlUp).Row
                x = Evaluate("=Match(""" & Data(i, 1) & """ & """ & Data(i, 2) & """,'" & .Name & "'!A1:A" & lr & "&'" & .Name & "'!B1:B" & lr & ",0)")
                If IsError(x) Then ii = ii + 1: Temp(ii, 1) = Data(i, 1): Temp(ii, 2) = Data(i, 2)
            End With
        Next i
    End With
    Sheets("Checked").Range("A" & Rows.Count).End(xlUp)(2).Resize(ii, 2) = Temp
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy/Paste unique
    By fmoro76 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-13-2021, 05:21 AM
  2. [SOLVED] Copy & Paste all unique values from a range
    By liamfrancis2013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2016, 07:09 AM
  3. [SOLVED] VBA Dynamic way to copy and paste unique values
    By Joakim1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-07-2014, 07:31 AM
  4. Compare two sheets,copy and paste unique rows based on values in 2 columns
    By ooggiemobile in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-02-2013, 03:58 AM
  5. Copy & Paste Unique Values
    By boiler2003 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2012, 09:08 AM
  6. Repeat Copy/Paste for Unique ID
    By dreicer_Jarr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2009, 12:03 PM
  7. Copy and paste unique values to another worksheet
    By gsrai31 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-28-2009, 11:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1