Find Adjacent Data In One Sheet and Copy/Paste Into Other Sheet

Hello all,

Basically I have this code:

Sub Macro2()

Cells.Find(What:="Lubricant A", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy Destination:=SheetCells.Find(What:="Lubricant A", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Offset(0, 1)
End Sub

There are two "Lubricant A" cells one in the first sheet (active sheet) and one in the 2nd sheet.

What I'd like to do is copy the cell adjacent to Lubricant A from the active sheet and paste it into the adjacent cell of the 2nd sheet, the above code is only for the Active sheet how can I modify it to do it for the 2nd sheet, any suggestions?

Attached is a file with what I'm looking at in trying to copy the above is just one example.

Thanks.

Originally posted here: http://stackoverflow.com/questions/7...to-other-sheet
Also posted here: http://www.ozgrid.com/forum/showthre...982#post578982