Search, Copy and Paste selections of a column

Hey,
I'm trying to write a macro with a button in the workbook that has the headings of all my column (which are in row 5) in a drop down menu. When a heading is selected I would like the macro to go the column with that heading and copy a selection below. The various columns are spread of multiple sheets.

So for example:

Apples Bananas Pears
1_________2______3
1_________2______3
1_________2______3

I would like a button with the options (Apples, Bananas, Pears), after selecting Bananas I would like it to copy and paste the column into column x.
Edit: I have also attached an example workbook.

Thanks.

Not sure if you mean a macro or data validation list...

Probably data validation...

Start by naming each columnar range.. so select one columnar range and type the name you want to appear in the dropdown (the column headings) into the empty field to the left of the formula bar (i.e. into the Name Box)... do the same for each.

The go to the cell you want the dropdown box in, and go to data|validation and select List from the Allow menu and then in the source field enter the range of headers of the columns.

Then click Ok.

Then in cell under the data validation, enter formula:

=IF(ROWS($A$1:$A1)>COUNTA(INDIRECT(A$1))-1,"",INDEX(INDIRECT(A$1),ROWS($A$1:$A2)))

and copy down as far as you want.

Now when you select a new item, it should autoupdate your results.

Ok I have made the drop down menu and filled the space below with you formula but the cells don't have an value in them, what am i doing wrong?

Edit: got the values but not everything is correct, so I'm currently tweaking...

I have been in meetings all morning here... and am going to be busy this afternoon too.. so not sure how much time I can spend.

Your named ranges must not have spaces in them, they could have underscores...

Then you would have to create a separate list somewhere of all the possible column names to include in your dropdown menu.

Then you would substitute the formula above with:

Please Login or Register  to view this content.

This will substitute the name chosen in your dropdown with a version of the name with underscores instead of spaces and they should match a column name to search into.

If done correctly, the blanks should return as blanks and not 0's.

Once you attempted this, post the sample workbook if it is not working....and I (or someone else) will try to assist... maybe someone has another idea... vba perhaps?

If the columns were all in one sheet, you would not perhaps need the named ranges... and column name alterations....

trying it now

Edit: I don't seem to be able select cells on different sheets than the data validation.