Macro to compare data between 2 sheets and copy differences over

I am looking to construct a macro that compares two worksheets in a workbook and copy differences over (updates) into one of the sheets that is being compared. Basically, when a file is imported,  the differences need to be copied over into the "Current" tab from the "Import" tab to have the file updated. If it is possible to highlight these differences in the "Import" tab, that would be more beneficial to the user before it is copied over to the "Current" tab.
Report.xlsx
AckeemKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Few questions...

1. On Basis of which column you want to compare as in the matching key in both the worksheets?
2. Do you want to compare the entire entry or just one particular column or 2 columns.. If just few columns then what are those columns are?
3. If the difference is found.. Do you want to copy or rather update those new entry to current tab from import tab?
0
AckeemKAuthor Commented:
I want to use the ID/SR number column as the unique key to compare between the sheets. I want to compare the entire row against each other and if there are any differences from the imported file within the 'Import' tab then it is updated in the 'Current' tab and the user can see those changes (highlighted possibly) until the file is saved. Most times, everything will remain the same except status, severity and last updated I believe.
0
Saurabh Singh TeotiaCommented:
You can use the following code..Assuming in import the ticket only shows once not multiple times..this will do what you are looking for...

In additional it will highlight the cell changes in red color whatever it updates...

Sub compare()
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim lrow As Long, lr As Long
    Dim lcol As Long
    Dim z As Long
    Dim rng As Range, cell As Range, r As Range, r2 As Range
    Set ws = Sheets("Current")
    Set ws1 = Sheets("Import")
    lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    lcol = ws.Cells(1, Cells.Columns.Count).End(xlToLeft).Column

    Set rng = ws.Range("A2:A" & lrow)
    Set r = ws1.Range("A2:A" & lrow)

    For Each cell In rng
        If Application.WorksheetFunction.CountIf(r, cell.Value) = 1 Then

            Set r2 = r.Find(What:=cell.Value, After:=ws1.Range("A2"), SearchOrder:=xlByRows, SearchDirection:=xlNext)
            z = 2
            Do Until z > lcol

                If cell.Offset(0, z - 1).Value <> ws1.Cells(r2.Row, z) Then
                    cell.Offset(0, z - 1).Value = ws1.Cells(r2.Row, z)
                    cell.Offset(0, z - 1).Interior.ColorIndex = 3
                End If
                z = z + 1
            Loop

        End If
    Next cell
End Sub

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

AckeemKAuthor Commented:
How can I have this macro move any new rows over from the "Import" tab to the "Current" tab if it does not exist there? This will let the user know upon updating their file that a new SR has been added.
0
Saurabh Singh TeotiaCommented:
You need to add a second layer in the code where you repeat this process again on the import tab to update new SR number in the current tab which essentially be a different macro from the current one since you will be calling that post completion of this macro...
0
AckeemKAuthor Commented:
I am having some issue adding a second layer in the code. The major issue is around having the macro update new SR number in the current tab. Essentially, how do I have a macro be called post completion of another one?
0
Saurabh Singh TeotiaCommented:
Ackeemk,

Let's say you have two macros in your sheet module with the name of abc and def. Now from abc you want to call second macro def...Then you can simply do something like this...

sub abc
def
end sub

Sub def

End sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.