Adding Input box to VBA code

This might be an easy fix, but I don't know how to add an input box to collect a variable word for VBA to search with.

My main page, "CAA" has variable locations, in which a new sheet needs to be created to export the filtered data to... My crude macro so far below..

The goal is to extract specific information from the master data (CAA), which in this case is by state. Once the State has been filtered, I need to copy this data to another worksheet, so the new worksheet (Macro Test) will have only data for that particular state.

This needs to be done for all States. Short of writing a macro for each state, I would like an option where the macro will prompt for a filter requirement, where I can enter the state (eg VIC, NSW, WA, etc)... After hitting enter, the Macro continues along until the new worksheet is created with the states details.

If the below macro can be improved upon (which I am certain it can be), I'd also be very thankful .. however my main goal is incorporating an input field or drop down selection box to dynamically run the macro based on the selection/input. if entered data doesn't exist (eg CIV is entered instead of VIC), then the macro should ideally fail and return an error.

The data I am working with is quite sensitive, so I am unable to attach an example. Hope someone can help me out

Please Login or Register  to view this content.

Have you tried;

Please Login or Register  to view this content.

Not sure if it will do what you want, but may be a start.

Legend !

Is there something that can be written to pop up with an error stating "State not available" or something along those lines?

Actually, I'd like to take this a step further, and replace the inputbox with a drop down list based on the values within the column (in this case, the states) This would remove any typos or non-existent states.

Could this be done at all?