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.
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.
Code:
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
Last edited: