Filter, copy filtered data into another sheet, create a file of that sheet..

Hi there,

I have been searching the forum now for a while on filtering through VBA but the examples cited don't match what I am looking for, and I am not that good at VB to modify what I found to meet my requirements.

Here is what I need to do.

I have a list of items assigned to a person. There are a few persons in the list, each with different items belonging to them.

1. I would like to create a single sheet for each person, listing his items. If the sheets already exist (running the macro for the 2nd time, the contents should be erased and recreated)
2. I would like to have each person have his own excel FILE, so I can email it to them (e.g. peter.xls, maria.xls, frank.xls,...). Once again, if the excel file already exists, it can be deleted and replaced whenever the macro is run.

Attached please see an example.

NOTE: I don't want to manually autofilter in case anybody suggests that. This needs to be fully automated.

Thanks for your help, appreciate it!!
Titus

Just one more thing (sorry if it's too much to ask ;-).

Would it be possible to SORT the data in the created sheets and files in ASCENDING order by the number of items?

E.g.

2 shoes
3 horses
5 coffees

etc...

Thanks again ;-)
Titus

See attached file where I used this macro:

Please Login or Register  to view this content.

Regards,
Antonio

Wow, this is impressive! And it's working like a charm! Thanks so much, you're a genius!!

One more question though: if I have an Excel file with more columns of data, how I can get the macro to work, exporting files with all the other columns of data?

The logic stays the same: column1 contains the filename and column2 needs to be sorted by number.
Probably takes you 10 seconds, would mean hours to me ;-)

Thanks so much, this is really great!
Titus

This new version copy all the row in new sheets so it copies all columns.

Regards,
Antonio

I added label 'Color'.

Regards,
Antonio

Hello,
I am a somewhat advanced Excel user but a poor soul when it comes to macros... It would be extremely helpful if someone could tweak this macro - to do exactly what it does now - but instead of creating a new workbook file with the filtered data, to just extract the filtered data to another blank worksheet in the same workbook?
Also, once the extracted data is there... if the user again re-filters the data on the criteria page, and then clicks the "Run Macro" button, the old data on the destination sheet will delete, and the new filtered data will replace it?
This would be great if someone could help me out with this code... thank you very much!!
Matt