Splitting up a file

[SandsB]

I have a file with about 2500 rows. The top row is a header. Column A shows clients. One client can have anywhere from 1 row to 50+ rows.
I need a macro that'll split the large file up so each client has a single file (or tab in this worksheet - either would work) with just their info. EX: John Smith is the name that appears in 33 rows so a new file (or tab) is created that has the header row and the 33 rows of data where John Smith is in column A.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about

Sub SandsB()
   Dim Ary As Variant
   Dim i As Long
   
   With Sheets("Sheet1")
      Ary = Application.Unique(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
      For i = 1 To UBound(Ary)
         .Range("A1").AutoFilter 1, Ary(i, 1)
         Sheets.Add(, Sheets(Sheets.Count)).Name = Ary(i, 1)
         .AutoFilter.Range.Copy Range("A1")
      Next i
      .AutoFilterMode = False
   End With
End Sub

 

[SandsB]

Brilliant! Thank you

 

[Fluff]

You're welcome & thanks for the feedback.