Filter column and copy distinct entries into their own worksheet

[Jncrtny]

I have a consolidated worksheet of over a decades worth of information from a number of set locations. I have filtered the locations in Column I. My data has 25081 lines and runs through column M. I would like to Filter each location and designate a worksheet for each while also having the filtered location placed on the worksheet tab. Any help would be greatly appreciated!

On a side note I am trying to be thorough in my request due to the inability to publish my data and workbook.

[Richard Buttrey]

Hi,

Are you looking for a macro to do this or manually?

Just as a matter of interest why do you need subsets of the data on their own tabs when as you are aware you can just as easily filter the data to show each location.

[Jncrtny]

I would like to have a macro to do this. Cutting each location out and having it entered on it's own tab will allow for quicker retrieval, I need to be able to build individual reports for each location to be presented to the division that the information pertains to. Having the tabs seemed to be the best way to inventory the divisions in the report while also allowing for quicker retrieval. If you see a simpler/quicker way I am more than open to suggestions.

I really appreciate the response.

[Richard Buttrey]

Hi,

My preference always with these sorts of tasks is to keep things simple. There seems to me little point in creating subsets on individual sheets which only make the workbook much larger than it need be. My usual approach is as follows.

1. Create a named range (say "locations") consisting of unique list of locations on another tab. (If locations change and are added or deleted frequently then create a macro which automatically extracts a unique list and make the named range a dynamic range name.
2. In a data validation drop down cell above your data add a 'list' validation and specify your range of unique names.
3. In the cell above the drop down add the column label that contains your locations and name the two cell range consisting of the column label and drop down cell say "crit_location"
4. Name your 25000 row data range as say "Data"
5. In the Sheet Change event of the data sheet add the following macro

Please Login or Register  to view this content.

If you absolutely need separate worksheets then create something like the following (untested).

Please Login or Register  to view this content.