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

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

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

hope this makes sense


You don't need a macro to do that - you can do it with a VLOOKUP
formula. Put this in A10 of sheet2:


Then put this in B10:


and copy this across to E10.

Hope this helps.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question