malarvel
08-27-2016, 11:40 PM
I'm trying to compare three sheets in excel and have matching column data copy the entire row into a new fourth sheet.
Example:
Compare Sheet 1 column A (empid) with Sheet 2 column A(empid) and sheet3 column A(empid) if the record match, copy the entire row of matching sheet 2 & Sheet 3 column data to a Sheet 4.
I have written code but the doesn't work
Dim lRow, lrow2, Lrow3 As Long
Dim fValue As Range
Sheets("Sheet1").Select
lRow = Range("A10").End(xlDown).Row
lrow2 = Sheets("Sheet2").Range("A5").End(xlDown).Row
Lrow3 = Sheets("Sheet3").Range("A5").End(xlDown).Row
For Each cell In Range("A10:A" & lRow)
With Sheets("Sheet2").Range("A5:A" & lrow2)
Set fValue = .Find(cell.Value, LookIn:=xlValues)
If fValue Is Nothing Then
cell.EntireRow.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
With Sheets("Sheet3").Range("A5:A" & Lrow3)
Set fValue = .Find(cell.Value, LookIn:=xlValues)
If fValue Is Nothing Then
cell.EntireRow.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
Next cell
I have attached sample data for reference.
Any help would be appreciated
Example:
Compare Sheet 1 column A (empid) with Sheet 2 column A(empid) and sheet3 column A(empid) if the record match, copy the entire row of matching sheet 2 & Sheet 3 column data to a Sheet 4.
I have written code but the doesn't work
Dim lRow, lrow2, Lrow3 As Long
Dim fValue As Range
Sheets("Sheet1").Select
lRow = Range("A10").End(xlDown).Row
lrow2 = Sheets("Sheet2").Range("A5").End(xlDown).Row
Lrow3 = Sheets("Sheet3").Range("A5").End(xlDown).Row
For Each cell In Range("A10:A" & lRow)
With Sheets("Sheet2").Range("A5:A" & lrow2)
Set fValue = .Find(cell.Value, LookIn:=xlValues)
If fValue Is Nothing Then
cell.EntireRow.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
With Sheets("Sheet3").Range("A5:A" & Lrow3)
Set fValue = .Find(cell.Value, LookIn:=xlValues)
If fValue Is Nothing Then
cell.EntireRow.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
Next cell
I have attached sample data for reference.
Any help would be appreciated