Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying data from one workbook to another dependent on user select
I am inexperienced with Visual Basic.
I have a table in a workbook (wkbook1) which has a table similar to the following A B C D E 2005 2006 2007 2008 1 North 10 4 34 15 2 South 8 25 2 4 3 East 8 14 18 7 4 West 14 2 11 10 5 Belfast 17 13 20 8 6 NI 12 8 25 4 In workbook2 I want to copy over all the data for Belfast and NI which is straight forward with a recorded macro,(i.e. copy and paste A5:E5 and A6:E6) these ranges will not change However, I want to copy over the other data, dependent on what the user defines. Therefore if the user wants 'West', the user will type 'West' into workbook2 cell B1 and the macro will copy over the line of data with the cell value of 'West', A4:E4 in workbook 1 and paste it into workbook2 cell A10:E10. The user will only ever type, north, south, east or west into workbook2:B1 and the selection will always be pasted into the same cell range on workbook2:A10:E10 hope this makes sense |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying data from one workbook to another dependent on userselect
You don't need a macro to do that - you can do it with a VLOOKUP
formula. Put this in A10 of sheet2: =IF(ISNA(MATCH($B$1,sheet1!A$1:A$6,0)),"",VLOOKUP( $B$1,sheet1!$A$1:$E $6,1,0)) Then put this in B10: =IF($A10="","",VLOOKUP($A10,sheet1!$A$1:$E$6,COLUM N(B1),0)) and copy this across to E10. Hope this helps. Pete On Apr 19, 3:36*pm, sarahphonics wrote: I am inexperienced with Visual Basic. I have a table in a workbook (wkbook1) which has a table similar to the following * * * * * * *A * * * * *B * * * * * * C * * * * *D * * * * * E * * * * * * * * * * * *2005 * * * 2006 * * 2007 * * * 2008 1 * * * * North * * *10 * * * * * 4 * * * * *34 * * * * *15 2 * * * * South * * * 8 * * * * * 25 * * * * *2 * * * * * 4 3 * * * * East * * * * 8 * * * * * 14 * * * * 18 * * * * *7 4 * * * * West * * *14 * * * * * *2 * * * * *11 * * * * *10 5 * * * * Belfast * *17 * * * * * 13 * * * * 20 * * * * * 8 6 * * * * NI * * * * *12 * * * * * *8 * * * * * 25 * * * * *4 In workbook2 I want to copy over all the data for Belfast and NI which is straight forward with a recorded macro,(i.e. copy and paste A5:E5 and A6:E6) these ranges will not change However, I want to copy over the other data, dependent on what the user defines. Therefore if the user wants 'West', the user will type 'West' into workbook2 cell B1 and the macro will copy over the line of data with the cell value of 'West', A4:E4 in workbook 1 and paste it into workbook2 cell A10:E10. The user will only ever type, north, south, east or west into workbook2:B1 and the selection will always be pasted into the same cell range on workbook2:A10:E10 hope this makes sense |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Let User Select Sheet to Pull Data From | Excel Discussion (Misc queries) | |||
select dependent cells in the result cell | Excel Discussion (Misc queries) | |||
can you select paste area before copying data | Excel Discussion (Misc queries) | |||
Copying Rows dependent upon a given cell value | Excel Discussion (Misc queries) | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions |