Extracting data to another worksheet.

[Steve Jones]

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

 

[Guest]

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

 

[Simon Lloyd]

The easy way would be to name your range on your first sheet and insert
the formula below on your second sheet the first Vlookup would get
pasted in cell B1 the next in C1 and so on, you would need to do this
for each row so your next row would be =VLOOKUP(INDIRECT("A2"),YOUR
NAMED RANGE,2,FALSE) and so on
=VLOOKUP(INDIRECT("A1"),YOUR NAMED RANGE,2,FALSE)
=VLOOKUP(INDIRECT("A1"),YOUR NAMED RANGE,3,FALSE)
=VLOOKUP(INDIRECT("A1"),YOUR NAMED RANGE,4,FALSE)

Hope this helps

Simon.

 

[Steve Jones]

Thank you both very much for your help.

Regsrds

Steve