Sub Macro1()
Dim StkRng As Range
Dim lastRow as Long, cell as Range
Dim rw as Long
rw = 2
LastRow = Cells(Rows.count, "A").End(xlUp).Row
Set StkRng = Range("A7:A" & LastRow)
For Each Cell In StkRng
If Cell.Value = Worksheets("Sheet1").Range("a2").Value Then
cell.offset(0,1).Resize(1,3).copy _
Destination:=Worksheets("sheet2").Cells(rw,"B")
rw = rw + 1
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Steve Jones" wrote:

Hi

I asked the question in the Misc. group yesterday and am assuming that I
have phrased the question badly as there has been no response.

I have worksheet(1) with a table in columns A, B, C, D.
In the other worksheet(2) I have a list in cell A2 which refers to the names
in Column A of worksheet(1).

I would like to extract any data from columns B,C,D in worksheet(1),
providing Column A equals cell A2 in worksheet(2) and place it in rows in
worksheet(2).

I have created some code which displays an "OK" message each time there is a
match and this works on the data I have in the table, ie the OK displays the
right amount of times, but I am clueless when it comes to etracting the data
across to the other worksheet.

Sub Macro1()
Dim StkRng As Range
LastRow = Cells(Rows.count, "A").End(xlUp).Row
Set StkRng = Range("A7:A" & LastRow)
For Each Cell In StkRng
If Cell.Value = Worksheets("Sheet1").Range("a2").Value Then
MsgBox ("Ok")
End If
Next
End Sub

Thanks very much.

Steve