FIND a value from active cell in sheet(2),searching in sheet(1) then copy/paste adjacent cells to sheet(2)

[jimayers]

Hello all - I would like to click on a cell in sheet(2) which activates a macro.
The macro would take the value from the selected cell in sheet(2) search for it in sheet (1) then cop/paste the found cell and its adjacent cells back to sheet(2)
I am having trouble even getting the find toe work. Here is the latest of my tries:

Sub LookUp()


Dim pCDCR As Long
Dim pr As Integer
Dim pFind As Range
Set pCDCR = Range(ActiveCell).xlValue
Set SOMs = Sheets(1)
Set pFind = Find(What:=pCDCR, After:=SOMs, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
With Sheets(1)
Range(pFind.Row, 1).Copy
End With


With ActiveSheet
Range(ActiveCell.Row, 1).PasteSpecial
End With
End Sub

I appreciate any help - Jim A

 

[tygrrboi]

In Sheet2 code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'You might want to put some if statements to isolate when this is called so it does not call for every cell in sheet 2... 

Call LookUp(Target)


End Sub

Sub Lookup(Target as Range)
Dim pFind As Range

On Error goto ErrorSpot


Set pFind = Sheets(1).Range("[COLOR=#0000ff]wherever all your data in sheet 1 is stored[/COLOR]").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart).Resize(1, 2)

Target.Resize(1, 2).Value = pFind.Value

ErrorSpot:
Msgbox "Value not found in sheet 1."
End Sub

I assume adjacent means the cell to the right of the found value...

 

[jimayers]

That was so very clean and simple. I need to adjust it a little to get the exact right cells copied and pasted in the right location. With that, I do not know how to use .resize

So...once the value of the target cell (in sheet(2)) is found in sheet(1) I need the cells on either side (left and right) to be copied and then pasted into the cells on on either side of the original target.value cell.

Thanks - Jim A

 

[tygrrboi]

Try...
Set pFind = Sheets(1).Range("A1:Z20").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart).Offset(,-1).Resize(1, 3)

Change A1:Z20 to your actual search range

And then to paste...
Target.Offset(,-1).Resize(1, 3).Value = pFind.Value