Create worksheets for each employee with records in my file

[SandsB]

My file has up to 1,000 rows each day. There are about 15 volunteers and each of the rows of data lists the user ID of the volunteer responsible. One volunteer may have 50, another 200, another none on any particular day. And the volunteers may change for a while so a new guy might appear now and then.

If the volunteers' user ID's (ABC123, DEF456, etc) are in column D, how can a macro create a new file for each volunteer and contain just that volunteer's records without knowing the user IDs of the volunteers in advance?

 

[Logit]

Option Explicit

Sub Nits(): Dim r As Long, i As Long, wb As Workbook, EO As String, ws As Worksheet, K
Set ws = ActiveSheet: r = ws.Range("A1").CurrentRegion.Rows.Count
Application.ScreenUpdating = False
                    With CreateObject("Scripting.Dictionary")
                        For i = 2 To r: EO = ws.Cells(i, 4)
                        If Not .Exists(EO) Then .Item(EO) = i
                        Next i: K = .Keys()
                        For i = 0 To UBound(K)
        ws.Range("A1").CurrentRegion.AutoFilter Field:=4, Criteria1:=K(i)
        
                            Set wb = Workbooks.Add
        ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy wb.Sheets(1).Range("A1")
                wb.SaveAs ws.Parent.Path & "/" & K(i) & i: wb.Close
        
        ws.Range("A1").CurrentRegion.AutoFilter

Next i
End With
Application.ScreenUpdating = True
End Sub

 

[SandsB]

Sweet! Thank you

 

[Logit]

You are welcome.