Read good:

The macros on this webpage assume that your data is in a normal range.

If your data is in a List (in xl2003, Data>List) or Table (in xl2007, Insert Tab>Tables Group>Table)
see the macro examples on this page :http://www.rondebruin.nl/tablecode.htm
Or if you use Tables in Excel 2007 try the TableTools add-in : http://www.rondebruin.nl/table.htm

Important if your data is in a normal range:
1) Use headers in the first row of your data (1 Header row)
2) Don’t use empty or hidden rows in your data
3) Don’t use merged cells in your data
4) Turn AutoFilter or Advanced Filter off before you run the code

Do you wonder why I only use AdvancedFilter in the code to create the unique list and
not use xlFilterCopy to filter and copy the data to a new sheet or workbook ?
The reason why I use AutoFilter for that in the code is that there is a bug in xlFilterCopy
It will not copy the correct data when you have duplicate headers in the first row of your data.
Now with 16000+ columns in Excel 2007 the chance that this will happen is much bigger.

See the following pages if you just started with VBA

David McRitchie
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish
http://www.contextures.com/xlvba01.html

My own page
http://www.rondebruin.nl/code.htm



Code Examples

Try the code on the pages below or download the example workbook

Use AutoFilter to filter and copy the results to a new worksheet

Use AutoFilter to filter and copy the results to a existing worksheet

Create a new workbook for every unique value

Create a new sheet for all Unique values

Create a new sheet for all Unique values or paste it below the existing data if the sheet exists



Download Example workbook

You can download a example workbook here with all the code you find on the pages above.
Download Example workbook




More information


Visit Debra Dalgleish's site for very good information
http://www.contextures.com/tiptech.html

EasyFilter Add-in
http://www.rondebruin.nl/easyfilter.htm

If you use a List in 2003 or Table in 2007 see
http://www.rondebruin.nl/tablecode.htm

For Tables in 2007 check out this add-in
http://www.rondebruin.nl/table.htm