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?
 
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.