Note: The macros on this page assume that your data is in a normal range.

If your data is in a Table see the macro examples on this page : Tables in Mac Excel with VBA

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

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 used AutoFilter for that part is because of a bug in AdvancedFilter xlFilterCopy at the time I have created the code. It does not copy the correct data when you have duplicate headers in the first row of your data.

Download Example workbook
File Date: 23-Jan-2021

Examples in the workbook are:

• 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