Excel Macro to Repeat for Unique Values

[aelsn]

I have an Excel workbook containing 2695 rows with information pertaining to 26 individuals. My goal is to split the workbook into 26 separate workbooks which I can then save as individual files. Not being very versed in writing code, I recorded a macro where I filterd for one individual and copy/pasted the rows into a new workbook. My obvious problem is that when I run the macro, it works for the one individual I started with as opposed to repeating for each unique value in the column I filtered. Column J in my spreadsheet contains employee numbers unique to each individual. I have been searching previous threads and cannot find an answer if there is one. Is there a criteria I can use to have the macro run for each unique value? I started with "6531" as you can see below.

Sub Macro1()
'
Selection.AutoFilter Field:=5, Criteria1:="6531"
Rows("1:2660").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub

Thank you

 

[SkipVought]

Hi,

May I ask WHY you would chop your data up and save 26 separate workbooks?

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[aelsn]

I was hoping to attach the individual file to an email to be sent to each person on the sheet. Each person will receive an email containing this attachment and one other.

Thanks

 

[SkipVought]

Use Advanced Filter to create a Unique list of criteria values...

Sub Macro1()
    Dim ws As Worksheet, r As Range
    Set ws = ActiveSheet
    
    With ws
        For Each r In Range("YourListOfCriteriaValues")
            .[A1].AutoFilter Field:=5, Criteria1:=r.Value
            .[A1].CurrentRegion.Copy
            Workbooks.Add
            ActiveSheet.PasteSpecial xlPasteValues
            ActiveWorkbook.SaveAs r.Value & ".xls"
            ActiveWorkbook.Close
        Next
    End With
End Sub

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[aelsn]

I had to put this aside for now but I'll circle back later and see if this does it. Thanks for your time