Compile data from one sheet to another

Thank you Janos, for taking your time to reply. However this superb little dropdown feature does not quite cut my needs. The scenario is rather that a customer would order lets say 15 different fruits and I want these 15 displayed as rows on the ordersheet and these 15 costs summarized at the bottom. I later use this order sheet to save as a pdf and email to the customer (have a nice macro for that already ;-) so I don't benefit from dropdowns at this point.

I would like a macro that loops through the productsheet B column looking for values >0 and copy those rows, than go to the ordersheet to insert these rows below row 20, however many these may be.

Manually I would use a filter to filter out these orders in the product sheet and copy paste the rows to the order sheet, but the point of macros is having things automated right ;-)

Any idea on how to do this?

OOOOH Alan! Now we're talking =D Just posted my reply as you posted yours at the same time. You gave me a code that does it the way my thinking does. I tried copying the code into the code of the sheet (rightclick the sheet - view code) should I have it another way? How do I get this script to run when the sheet tab is selected/opened/switched to? It doesn't work when I pasted it into the sheet code ='( when I run it as a macro I only get the messagebox but no list is displayed.

If you have the time to assist me some more it would be grately appreciated! I'm getting so thilled now that I have hope of getting this fixed soon

Thank you Alan =) Sweet of you to give me a guick guide on how to insert the macro, I know this much, but it's not what I'm looking for (or I don't think I am?) I have made buttons that links to macros etc too. Now I absolutely don't want to appear ungrateful, but I would really like the macro to run automatically when I switch to the order sheet from the product sheet, don't want to have to use a button (the person this workbook is going to be used by is a real newbie who will forget to click it...) Is this possible? How do you run a macro when the sheet is opened? Can I put it in "ThisWorkBook" or the code of the sheet instead of in a module? Can I call the module some other way than using a button?

And also, your excellent file has no summary at the bottom. Fixable? I realized it was stupid of me to have merged cells in the ordersheet (missed that when i copied info...), that can be ignored and the row can just be pasted as it is in the same comlumndesign.

But thanx to you I'm closing in on a good solution, and for that I am happy =D

Thumbs up for you Alan

Hmmm.. Ok, so I Googled how to run a macro when a sheet is activated, so far so good. But... I now realized that I need to have the macro delete all previous rows before adding rows again when the macro runs or i will get a messed up orderlist, and there is no way of doing nice changes to the order. What i need is for the macro to delete all rows down from row 20 until it hits an empty row.

Grrr! How frustrating it is to know what you want done, but don't have the competense do write the code!

Alan if you have any patience left for me, I will be a happy camper!

Thank you so much for all the help!