Preventing copying duplicates from one workbook to another

[Teleporpoise]

Hello Mr. Excel Friends,

I am copying rows of data from various workbooks into one workbook. The data in the workbooks will be cleared out weekly, but will be collected daily. Therefore I will have the issue of duplicates with this code:

Private Sub GTB_Click()
    Dim LastRow As Integer
    Dim i As Integer
    Dim erow As Integer
    Dim GTBT As Workbook
    Dim Traceability As Worksheet
    
    Set Traceability = ThisWorkbook.Worksheets("Traceability")
    
    Workbooks.Open Filename:="\Libraries\Documents\WS1.xlsm"
    Set GTBT = Application.Workbooks("WS1.xlsm")
    GTBT.Worksheets("FR").Select




        LastRow = GTBT.Worksheets("FR").Range("A" & Rows.Count).End(xlUp).Row


        For i = 5 To LastRow
            GTBT.Worksheets("FR").Range(GTBT.Worksheets("FR").Cells(i, 1), GTBT.Worksheets("FR").Cells(i, 17)).Copy
            Traceability.Cells(Traceability.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            'ActiveWorkbook.Save
            'ActiveWorkbook.Close
            Application.CutCopyMode = True
            Application.CutCopyMode = False
        Next i
        GTBT.Close
End Sub

As you can see, I am copying the whole row of data. I have this same code multiple times in the sheet with references to different workbooks.

The other issue that I will encounter is that I want to prevent duplicates based on the info in the whole row. What I mean is, I might have duplicates in column A for two rows, but not column B, and so I wish to copy the two duplicate rows as the B values are different.

Can I avoid copying duplicates from the same sheet? Or can I have a code or macro for the whole sheet that finds duplicate rows and deletes them?

Thank you for your help

 

[Fluff]

Once you have copied data across,will that data be changed in the original workbook?
If not are you happy to add an extra column that shows the data has been collected?

 

[Teleporpoise]

Hi Fluff, the data will not be changed in the original workbook after being copied. I don't think I understand your second question though.

 

[Fluff]

Ok, how about when the macro runs it puts an X (or something) into column R.
Then when the macro is run again, it only copies rows that don't have an X in column R

 

[Teleporpoise]

Ok! I think I understand what you mean, I'll give it a try. Thanks!

 

[Fluff]

If you need a hand, just shout.

 

[Teleporpoise]

Hi Fluff, can you please provide me with a sample code on how I can do that? I tried but I'm unable to get mine to work and I'm not sure what to google.

 

[Fluff]

How about

Private Sub Telepopoise()
   Dim LastRow As Long, FirstRow As Long
   Dim GTBT As Workbook
   Dim Traceability As Worksheet
   
   Set Traceability = ThisWorkbook.Worksheets("Traceability")
   
   Set GTBT = Workbooks.Open(FileName:="\Libraries\Documents\WS1.xlsm")
   With GTBT.Worksheets("FR")
      firatrow = Range("R" & Rows.Count).End(xlUp).Offset(1).Row
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A" & FirstRow & ":A" & LastRow).Resize(, 17).Copy
      Traceability.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      .Range("R" & FirstRow & ":R" & LastRow).Value = "X"
   End With
   Application.CutCopyMode = False
   GTBT.Close
End Sub

Make sure that there is something in R4 in the GTBT file the first time you run this.