match cells and return another value

[stl]

Hi There, Thanks for this opportunity, I really need your help here. I have a workbook with 6 sheets. This is what I want to do:
If cell sheet1!f2 equals any cell in in column c of sheet6! then I want excel to copy the coresponding d,e,f,g,h,i cells
in sheet6! and paste them into sheet1! cells x,y,z,aa,ab,ac
and then do the same for sheets 2-5.
Please help as there is too much data to do this manually.

 

[manney]

I'm not clear exactly what you want to do after you find the match.

The code below assumes you want to copy the values of Sheet6, Columns D-I to

--Sheet1, same row as the value was found on Sheet6, Cols X-AC
--Sheet2, same as Sheet1
--...etc.

The code below also assumes your sheet1 is named "Sheet1", Sheet2 is named "Sheet2", etc.

Have fun.

=======================================

Sub Compare()

'Find extent of Column "C" in Sheet6
Sheets("Sheet6").Range("C1").Activate
Selection.End(xlDown).Activate
iBottomofofColC = ActiveCell.Row

'Step through Sheet6, Col "C" values
For iRow = 0 To iBottomofofColC - 1
'Check if current row, Col "C" is equal to Sheet1 Cell F2
If Sheets("Sheet6").Range("C1").Offset(iRow, 0).Value = _
Sheets("Sheet1").Range("F2").Value Then

'Copy Current Row, Cells "D" to "I" to same row,
' Sheet1, Cells "X" to "AC", sheets 1 through 5
For iSheet = 1 To 5 'Step through Sheets 1-5
For iCol = 0 To 5 'Step through Columns
Sheets("Sheet" & iSheet).Range("X1").Offset(iRow, iCol).Value = _
Sheets("Sheet6").Range("D1").Offset(iRow, iCol).Value
Next iCol
Next iSheet
End If
Next iRow

End Sub