+ Reply to Thread
Results 1 to 4 of 4

Copy Rows - Ignore duplicates

Hybrid View

  1. #1
    Registered User
    Join Date
    11-16-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Copy Rows - Ignore duplicates

    Hi there,

    I've created a macro which will send all rows from a worksheet to another worksheet in a different workbook. Currently it will send everything from column A - Column U until there's an empty row.

    I was wondering if there is a way to skip duplicate rows. So for example if the cells in row A2:U2 matched a row in the second workbook, it would skip sending that row!

    Below is the code I'm using, and I've attached an example spreadsheet to give a clearer view of what I'm trying to achieve.

    Thanks for any help in advance

    Sub uploadmacro()
        Dim SourceRange As Range
        Dim DestRange As Range
        Dim DestWB As Workbook
        Dim DestSh As Worksheet
        Dim Lr As Long
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
    
        If bIsBookOpen_RB("mastersheet.xls") Then
            Set DestWB = Workbooks("mastersheet.xls")
        Else
            Set DestWB = Workbooks.Open("T:\Folder\Folder2\mastersheet.xlsm")
        End If
        Set DestSh = DestWB.Worksheets("masterachieved")
    
    Lrs = LastRow(ThisWorkbook.Sheets("examplesheet"))
    
    For T_ros = 2 To Lrs
        Set SourceRange = ThisWorkbook.Sheets("examplesheet").Range("A" & T_ros & ":" & "U" & T_ros)
    
        Lr = LastRow(DestSh)
        Set DestRange = DestSh.Range("A" & Lr + 1)
    
        With SourceRange
            Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
        End With
        DestRange.Value = SourceRange.Value
    Next T_ros
    
        DestWB.Close savechanges:=True
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy Rows - Ignore duplicates

    Would it not be easier to just append the data and then remove duplicates?
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows - Ignore duplicates

    I agree with Winon. You can add the data in and if there is a unique field or combination of fields, we can delete the duplicates. Its far easier and faster than checking if its duplicate and then copying.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy Rows - Ignore duplicates

    @ arlu,

    Hello Arlette,

    I am glad to see there is someone who agrees with my suggestion. Let us just hope it will be favourably considered.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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