Copying Data to a Master sheet from other sheets without duplicates

Question

I have four workbooks, three of which are for data entries, and the latter which hosts all data entered in the other workbooks; as a matter of fact, it serves as a master workbook. I need code to copy data entered in each workbook in sheet 1 to be copied to the next available row on sheet 1 of the master workbook, but if the data to be copied from these individual workbooks are duplicates, then a message box should pop up displaying that its a duplicate. To be precise, I want the users to type p in column j indicating process, and click a button in the spreadsheet to copy the data to the master sheet.

I would be glad for any assistance. Thanks a million for any help.

Does the user have to take any action when a duplicate is identified or is the message box just for information? — barrowc
barrowc when the user gets the message box then user have to click ok and lookup the entry. i have an arraye formula i will set up to lookup the master sheet from any individual workbook,but if there is any suggestion of best fit i will go by that thanks again — sam
hi all, i want to clarify my initial question for a possible solutions from any savior out there. Say i have 3 excel sheets one for each user for data entry located in separate workbooks to avoid excel share workbook problems. I also have a master sheet in another workbook where i want individual data enter on those sheets precisely sheets 1 should copy to the next available row of sheet 1 in the master sheet as the users enter them. i need a vba code that can do without copying a duplicate row in the mastersheet but highlight the duplicate row and lookup the initial record in master — sam
Please give more definition on what would make a row a duplicate; i.e., a specific cell being duplicated, or the entire row exactly duplicated, or something else. — Lance Roberts

Alpha Alpha · Accepted Answer · 2012-04-15T01:48:48

Public Sub CopyData()

    ' To use Dictionary, add Microsoft.Script.Runtime in Tools->Reference
    Dim dataDict As New Dictionary
    Dim ws As Worksheet, i As Integer, r As Long, lastR As Long, c As Long, keyString As String

    lastR = 2
    For i = 1 To 3

        Set ws = Workbooks.Open("Your file").Worksheets(1)

        ' Step 1. Loop through all the rows

        r = 2 ' Assume data starts at row 2
        Do While ws.Cells(r, 1) <> ""

            ' Step 2. Check duplicates

            ' Step 2.1 Generate keys
            c = 1
            keyString = ""
            Do While ws.Cells(r, c) <> ""
                keyString = keyString & ws.Cells(r, c)
                c = c + 1
            Loop

            ' Step 2.2 Now check duplicates in dictionary
            If dataDict.Exist(keyString) = False Then

                ' Copy data here

                ' Increment last row in the master workbook
                lastR = lastR + 1

            Else

                ' Duplicate found - Highlight or do whatever here

            End If

            r = r + 1

        Loop

    Next i

End Sub

Copying Data to a Master sheet from other sheets without duplicates

1 Answers