Copying data from one workbook to another dependent on user select

[sarahphonics]

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

 

[Pete_UK]

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,COLUMN(B1),0))

and copy this across to E10.

Hope this helps.

Pete