Macro to select, copy, and, paste multiple categories.

**rygon8** · 03-30-2011, 12:43 PM

Hello,

I have a macro project that I'm working on. The attached spreadsheet has a pdf, results, data, and list tab. There is a macro built in to the purple button that allows me to search a category from the data tab, and will paste the results (in the results tab). The coding problem I'm having is that I need to be able to select multiple categories at once, for example, standard DME and complex rehab. I've attached the spreadsheet with the macro. Any help with this would be greatly appreciated.

Thank you.

**MarvinP** · 03-30-2011, 01:03 PM

Hi rygon8 and welcome to the forum,

I have a suggestion. Instead of using a Macro, how about using simple AutoFilters on your Data Tab?
Using Excel 2010, click on your Data Tab and then click anywhere in your table. Click on the Data Tab (at the top of the screen to view the Excel Icons and click on "Filter". This will put dropdowns on each of your column headings. Then click on the Category dropdown and select multiple filters. This will give you a list just like your Results Tab.

If you need a separate Results tab to show your data and can't use AutoFilters you should look at Advanced Filters using the OR condition. That would filter Categories like you discribe above. Here is a good site to show AND and OR examples. http://www.contextures.com/xladvfilter01.html

**rygon8** · 03-30-2011, 01:31 PM

Originally Posted by MarvinP

Hi rygon8 and welcome to the forum,

I have a suggestion. Instead of using a Macro, how about using simple AutoFilters on your Data Tab?
Using Excel 2010, click on your Data Tab and then click anywhere in your table. Click on the Data Tab (at the top of the screen to view the Excel Icons and click on "Filter". This will put dropdowns on each of your column headings. Then click on the Category dropdown and select multiple filters. This will give you a list just like your Results Tab.

If you need a separate Results tab to show your data and can't use AutoFilters you should look at Advanced Filters using the OR condition. That would filter Categories like you discribe above. Here is a good site to show AND and OR examples. http://www.contextures.com/xladvfilter01.html

Hi MarvinP,

Unfortunately filters will not work for this project. It needs to be more powerful (or appear to be) for the end user. The end user (a very particular VP) has very little Excel experience and has no real desire to learn any features in Excel. They need to click on a button, have the result, and then save to PDF.

Thank you for your reply.

**MarvinP** · 03-30-2011, 02:13 PM

Can you use Advanced Filters at the top of the Results Page?

You could have two levels of Category and then press the big purple button.

Did you look at Advanced Filters?

**rygon8** · 03-30-2011, 03:26 PM

Originally Posted by MarvinP

Can you use Advanced Filters at the top of the Results Page?

You could have two levels of Category and then press the big purple button.

Did you look at Advanced Filters?

Hi

Yes, I have tried the advanced filters. That will not satisfy the end user's requirements. I need to be able to select any or all of the categories in a simple way (drop down menu), I realize that I will have to add in multiple rows so that the user can select which categories they wish.

Thank you again.

**MarvinP** · 03-30-2011, 06:31 PM

I thought I had attached this file. See if this works.

**rygon8** · 03-30-2011, 07:45 PM

Originally Posted by MarvinP

I thought I had attached this file. See if this works.

Mr. MarvinP

So that is is what you meant about the advanced filtering, that was way over my head. Thank you, this works perfectly. I think I can modify the code from there if needed.

Very much appreciated.