Auto filter based on user input with multiple choices

Dear all,

I have here an example of how I might extract data from a source workbook and paste it onto a template workbook, using autofilter to find the rows that are needed. It is currently set-up to take rows that match "AREA 1" or "AREA 2" in column A, along with a number of other criteria in other columns.

What I want to do is add into this macro, a way for the user to be able to choose the filter options will be used for column A of the source workbook. So if they want only "AREA 1", they can choose just that, or if they want "AREA 2", or perhaps both "AREA 1" & "AREA 2", or further additional options. They need to be able to specify just one, multiple, or all of the filter options with regards to column A of the source data.

In effect what I need is a user input box or form with a dropdown menu that will give the filter choices based on column A of the source data, and then for the code that copies the data across, to do so based on which choices the user makes in the filter menu of the input box.

I have uploaded examples of the source data workbook and the target template workbook. The macro exists within the template workbook.

Please Login or Register  to view this content.

I am looking for someone to demonstrate to me in this example, how it may be possible for the user to be able to specify, one, multiple, or all available filter options for column A of the source data, via a user input box or form.

Hopefully I have explained clearly enough but if not, please ask me questions. If anyone could assist I would be most grateful..

Many thanks,

Maybe:

Please Login or Register  to view this content.

Hi,

Thanks for that, yes this certainly does the job if the user can type in the required criteria and if I know how many different possible ones there are. My actual problem here is a bit more complicated than that....

For this to be useful for what I am trying to do, I will have to assume that the user does not know what the available filter options are, that I must present them a list of options from which they can choose 1, some, or all of the available options. The total number of options they choose may vary, as might the number of options that will need to be presented.

If you look at the attached Sourcedataexample, there is a drop down menu at the top of column A which allows you to filter the sheet by that column, by ticking the boxes next to the ones you want. You can choose just one, some or the whole lot. I need to present this same set of choices somehow to the user when the macro is run, and then take that into account when coping the data to the template. The options presented to the user needs to reflect the options that happen to be available at the time in column A of the source data.

I need to consider that the content of the source data could change and so the number of options that would need to be presented changes also. If a new area name was added to column A of the source data that didn't already exist, the next time the macro is run I will need that option to also be included in the list of options presented to the user.

I only need to do this for field 1 / column A, fields 4 & 6 in the above code can remain as they were in my example as the required criteria for those is always the same.