Results 1 to 8 of 8

Thread: Preventing copying duplicates from one workbook to another
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Preventing copying duplicates from one workbook to another

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,961
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    42 Thread(s)

    Default Re: Preventing copying duplicates from one workbook to another

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing copying duplicates from one workbook to another

    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.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,961
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    42 Thread(s)

    Default Re: Preventing copying duplicates from one workbook to another

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing copying duplicates from one workbook to another

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

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,961
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    42 Thread(s)

    Default Re: Preventing copying duplicates from one workbook to another

    If you need a hand, just shout.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing copying duplicates from one workbook to another

    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.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,961
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    42 Thread(s)

    Default Re: Preventing copying duplicates from one workbook to another

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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