Compare two worksheets and paste differences to another sheet - Excel vba free download

Hi, I need to write a VBA macro to compare all except 1 cell in 2 worksheets and the rows which have differences in cell values should be pasted in a new worksheet with the different cells highlighted. So far I have done a vlookup in sheet1 (Hdr-CV40) with column B in sheet2 (Hrd-Loaded) and then filtered the values in sheet1 that are not equal to #N/A. I have then sorted the data in sheet1 and sheet2 so that the rows in both sheet match. However, I am not sure how to proceed with comparing the cell values in these 2 sheets and then pasting it to the new sheet (Hdr-Mismatch). Any help would be highly appreciated.

Sub MismatchData() Dim SourceLastRow As Long Dim OutputLastRow As Long Dim ResultLastRow As Long Dim sourceBook As Workbook Dim sourceSheet As Worksheet Dim outputSheet As Worksheet Dim resultSheet As Worksheet Dim pasteRange As Range Dim saveSource As Boolean Application.ScreenUpdating = False 'what are the names of our worksheets? Set sourceSheet = ThisWorkbook.Worksheets("Hdr-Loaded") Set outputSheet = ThisWorkbook.Worksheets("Hdr-CV40") Set resultSheet = ThisWorkbook.Worksheets("Hdr-Mismatch") 'Determine last row of source With sourceSheet SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With With outputSheet 'Determine last row in col CP OutputLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Apply our formula .Range("CP2:CP" & OutputLastRow).Formula = _ "=VLOOKUP(B2,'" & sourceSheet.Name & "'!$B$2:$B$" & SourceLastRow & ",1,0)" 'Filter the data .Range("$A$1:$CP$" & OutputLastRow).AutoFilter Field:=94, Criteria1:="<>#N/A" 'Sort the data Range("A1:CP" & OutputLastRow).Sort _ Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, MatchCase:= _ False, Orientation:=xlTopToBottom saveSource = True End With Application.ScreenUpdating = True End Sub

I wasn't sure that did You really mean to compare sheets 1 & 2 except 1st row. Anyway ... here You are.

I can modify this after You'll give more details, Okay?

Thanks Vletm. In the 2 sheets I only want to compare the filtered and sorted vlookup data in Sheet1 (Hdr-CV40). Sheet2 is a subset of sheet1 so I only want to compare records where column B in both sheet matches. Also, column L should not be compared as it will always be blank in Sheet1 and populated in Sheet2. Also, in the output sheet is it possible to paste the header from Sheet2 and only the Sheet2 rows with mismatch values along with formatting and column widths?

I have created a sample output file that may explain this better.

Hi, easier with a result worksheet !

No need to filter Sheet1 with this demonstration :​

Sub Demo() Dim Rg As Range Set Rg = Sheet1.Cells(1).CurrentRegion.Columns(2) Application.ScreenUpdating = False With Sheet4.UsedRange: .RowHeight = Sheet4.StandardHeight: .Clear: End With L& = 1 With Sheet2.Cells(1).CurrentRegion.Rows .Item(1).Copy Sheet4.Cells(1) For R& = 2 To .Count V = Application.Match(.Cells(R, 2).Value, Rg, 0) If IsNumeric(V) Then B% = 1 For C& = 1 To .Columns.Count If C <> 12 And .Cells(R, C).Value <> Sheet1.Cells(V, C).Value Then If B Then B = 0: L = L + 1: .Item(R).Copy Sheet4.Cells(L, 1) Sheet4.Cells(L, C).Interior.ColorIndex = 36 End If Next End If Next End With Set Rg = Nothing Application.Goto Sheet4.Cells(1), True Application.ScreenUpdating = True End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Hi, I have another requirement that has come up for comparing sheets. It is possible that some columns that are blank in sheet1 (Hdr-CV40) are populated in sheet2 (Hdr-Loaded). In such a scenario we do not want to compare the columns. Is it possible to adjust the macro such that it only compares columns where sheet1 cell is not null? This needs to be done only for records where column B in both sheet matches. Attached is the sample sheet.

Also, I am trying to learn macro and it will be great if you can help me with some comments in the code so that I can extend the macro to a few other files without bothering you again.

If C <> 12 And .Cells(R, C).Value <> Sheet1.Cells(V, C).Value Then

If C <> 12 And .Cells(R, C).Value <> Sheet1.Cells(V, C).Value And Sheet1.Cells(V, C).Value > "" Then

Application.Match refers to MATCH Excel worksheet function (so see Excel's help) …

IF B Then means IF B <> 0 Then …

​

Thanks you so much