Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting data to another worksheet.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting data to another worksheet.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting data to another worksheet.
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. -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=564675 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting data to another worksheet.
Thank you both very much for your help.
Regsrds Steve "Steve Jones" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting data from one worksheet based on another - VLookup? | Excel Discussion (Misc queries) | |||
Extracting data from one worksheet to another? | Excel Discussion (Misc queries) | |||
Extracting data into another worksheet | Excel Discussion (Misc queries) | |||
Extracting data from large worksheet | Excel Worksheet Functions | |||
Ignoring Rows When Extracting Data From One Worksheet To Another | Excel Worksheet Functions |