Preventing copying duplicates from one workbook to another

Teleporpoise

New Member
Joined
May 23, 2019
Messages
20
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:

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,004
Office Version
365
Platform
Windows
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

New Member
Joined
May 23, 2019
Messages
20
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,004
Office Version
365
Platform
Windows
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

New Member
Joined
May 23, 2019
Messages
20
Ok! I think I understand what you mean, I'll give it a try. Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,004
Office Version
365
Platform
Windows
If you need a hand, just shout.
 

Teleporpoise

New Member
Joined
May 23, 2019
Messages
20
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,004
Office Version
365
Platform
Windows
How about
Code:
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.
 

Forum statistics

Threads
1,081,574
Messages
5,359,707
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top