AutoFilter Based on User Input Selection

[shoddy]

Hi All

I am trying to write a macro the autofilters based on the user's selection. Here is an example.

Column A Column B
Row 1 Snacks SN1
Row 2 Snacks SN2
Row 3 Clothes CL1
Row 4 Clothes CL2
Row 5 Drinks DR1
Row 6 Drinks DR2

The user would run the macro and a message box/input box would pop up and state "What need would you like to feel" along with a drop down menu or something like that would show "Snacks" "Clothes" "Drinks" The user could then select one or all options, the macro would then copy and paste the result in another spreadsheet. Note - column A could changed from time to time so I couldn't use constants. Please help. Thanks

 

[wigi]

And it's probably too easy to let the user use the AutoFilter built in in Excel?
Why recreating the autofilter again in your VBA-code? It already exists!

 

[shoddy]

Thanks for the reply Wigi. I probably should have mentioned the macro I have already does quite a lot before it gets to this point and after. I have seen posts where the user can type information into an input box and the macro will filter for example on column A, but couldn't find anything that gives the user options if they don't know what is available in column A. Hope this helps.

 

[shoddy]

Thanks for the reply Wigi. I probably should have mentioned the macro I have already does quite a lot before it gets to this point and after. I have seen posts where the user can type information into an input box and the macro will filter for example on column A, but couldn't find anything that gives the user options if they don't know what is available in column A. Hope this helps.QUOTE]
I should also add that the difference in output can be quite large, my actual file deals with trading activity. Looking at my example, the output file if drinks and snacks are selected might be 500 rows, whereas if there is the no filter the output might be 500,500 rows because of clothes. The user could in the end filter to get the 500 rows, but I want to avoid producing a space consuming file that is 500,500 rows when the user really only wanted 500. Hope this helps.