Parsing out file into multiple new files

shorter, faster, Col AA doesn't matter

Sub scode()
Const cl& = 5
Dim a As Variant, q As Variant
Dim rws&, cls&, p&, i&, b As Boolean
Application.ScreenUpdating = False
With Sheets.Add(after:=Sheets("Original"))
    Sheets("Original").Cells(1).CurrentRegion.Copy .Cells(1)
    Set a = .Cells(1).CurrentRegion
    rws = a.Rows.Count
    cls = a.Columns.Count
    a.Sort a(1, cl), 2, Header:=xlYes
    .Name = a(2, cl)
    a = a.Resize(rws + 1)
    p = 2
    For i = p To rws + 1
        If a(i, cl) <> a(p, cl) Then
            If b Then
                Sheets.Add.Name = a(p, cl)
                .Cells(p, 1).Resize(i - p, cls).Cut Cells(2, 1)
                Sheets("Original").Cells(1).Resize(, cls).Copy Cells(1)
            End If
            b = True
            p = i
        End If
    Next i
End With
Application.ScreenUpdating = True
End Sub

hi,
am also looking the same solution, one file to multiple files.
if you got any good result, please give to me also...

thanks
Josh

@Jerry B
Thanks for the considered response.
Please read the last part of these comments, even if not the rest.

1. Errors. Depends what errors are to be checked. In this type of problem the likely one is trying to re-create an already-existing worksheet. What happens in such case depends on the user, e.g. replace existing worksheet, or append data at the bottom, or leave the existing sheet as it is, or create a new sheet with an easily-identifiable similar name, or ...
Allowing for any of these is easily included in the code I posted with little increase in length. But I'd need to know which is wanted. Do you know which one is wanted? So what does your error checking actually check?
Easy and short enough to give a message box saying "Sheet already exists" or similar, but since the VBA tells you this anyway, what additional purpose is there in including it? It's errors that don't give error messages, such as in your own code, that concern me a lot more. See below.


2. Loops can be either explicit, or implicit as with the filter type approaches. Whichever way, all rows for this type of problem need be checked, so seems should come down to which approach is more efficient.
I used shorter and faster as efficiency criteria. Do you prefer others?

Faster. I wouldn't make such claim without checking it first.
So, generated a bit of test data with the code below. You can easily check anything I say here with this, or your own test data.

With the 5000 rows and 8 unique names you refer to, my code took 0.14 secs on an average spec notebook. Your code took just about twice as long.

Probably not much in that for the average problem.
But, with 100,000 rows and 8 names, my code took 1.4 secs. Yours took quite a bit longer, but also failed to give the correct result, failing without any error message. Try it and see.

Since you raised the topic of error checking, and wishing you no disrespect, wouldn't you regard it as reasonable to check that your own codes perform without error, or at least give some error message when they do go wrong?
So that a user might at least have some reasonable indication, preferably in advance of using it, as to when the code will work and when it won't.

Sub testdata()
Sheets("Original").Activate
ActiveSheet.UsedRange.ClearContents
Const n As Long = 10^5  'also tried with 5000
With Cells(1).Resize(n, 10)
    .Cells = "=char(int(rand()*26)+65)&randbetween(1,9)"
    .Resize(, 1).Offset(, 4) = "=rept(char(int(rand()*8)+65),4)"
    .Resize(1) = "=""Hdr"" & column()"
    .Value = .Value
End With
Cells(5) = "NAME"
End Sub

Hi, sorry you carry on.