Compare two worksheets

**Farooque** · 11-03-2011, 01:56 PM

Hello every body

How can I compare two different sheets or ranges in same files or different files.

**arlu1201** · 11-03-2011, 03:08 PM

There should be some common field or fields on the basis of which you can compare. Probably if you attach a sample file(s), i can help better.

**Farooque** · 11-03-2011, 09:58 PM

Dear
Both sheet having same format, only data changed
sample is attached

**arlu1201** · 11-05-2011, 05:02 AM

So as per the attached files, what do you want to compare? Each and every data? Also, if we find a mismatch, what should be done?

**Farooque** · 11-13-2011, 12:09 AM

Sorry for too much delay, I want to check changes (if any) in todays sheet comparing with yesterdays sheet. Todays sheet may highlight the changes in a seperate sheet or any other function which shows the changes

Waiting your useful feedback
Thanks

**arlu1201** · 11-20-2011, 08:41 AM

This code brings out the differences between Summery and Summery (2). The differences are highlighed in the Results tab. Is this what you are looking for?

Sub compare_sheets()

Dim str As String
Dim cRow As Integer

Sheets("Summery").Range("A2:Z3").Copy Sheets("Results").Range("A1")

With Sheets("Summery")
    For cRow = 4 To .Range("A" & .Rows.Count).End(xlUp).Row
        str = str & Join(Application.Transpose(Application.Transpose(.Cells(cRow, 1).Resize(1, 26))), "|") & "~"
    Next
End With
With Sheets("Summery (2)")
    For cRow = 4 To .Range("A" & .Rows.Count).End(xlUp).Row
        If InStr(1, str, Join(Application.Transpose(Application.Transpose(.Cells(cRow, 1).Resize(1, 26))), "|") & "~", vbTextCompare) = 0 Then
            .Cells(cRow, 1).Resize(1, 26).Copy
            lrow = Sheets("Results").Range("B" & Rows.Count).End(xlUp).Row
            Sheets("Results").Range("A" & lrow + 1).PasteSpecial
        End If
    Next
End With

End Sub