Splitting names in to sheets in alphabetical order

Hello catchnanan,

I have added a button to the "Names" sheet. It will run the macro below which creates a new workbook for each name. The serial number appears in "A1" and row 2 will display the name and information associated with it.

Sub CreateWorkbooks()

    Dim Data As Variant
    Dim Filepath As String
    Dim Rng As Range
    Dim Row As Long
    Dim Wkb As Workbook
    Dim Wks As Worksheet
    
        Filepath = "C:\Users\Owner\Documents\Excel Forum\catchnanan"
        Set Rng = Worksheets("Names").Range("B2").CurrentRegion
        
            For Row = 1 To Rng.Rows.Count
               Data = Application.Index(Rng.Rows(Row), 1, 0)
               Set Wkb = Workbooks.Add(xlWBATWorksheet)
               Wkb.Sheets(1).Range("A1") = Row
               Wkb.Sheets(1).Range("A2").Resize(1, UBound(Data, 2)).Value = Data
               Wkb.SaveAs Filepath & "\" & Data(Row, 1) & ".xlsx"
               Wkb.Close
            Next Row
    
End Sub

Change to

Sub test()
    Dim rng As Range, a, i As Long, temp As String
    Application.ScreenUpdating = False
    Set rng = Sheets("names").Range("b2").CurrentRegion
    a = rng.Columns(1).Value
    With CreateObject("System.Collections.SortedList")
        For i = 1 To UBound(a, 1)
            temp = UCase$(Left$(a(i, 1), 1))
            If temp <> "" Then
                If Not .Contains(temp) Then
                    Set .Item(temp) = rng.Rows(i)
                Else
                    Set .Item(temp) = Union(.Item(temp), rng.Rows(i))
                End If
            End If
        Next
        For i = .Count - 1 To 0 Step -1
            If Not IsSheetExists(.GetKey(i)) Then
                Sheets.Add().Name = .GetKey(i)
            End If
            Sheets(.GetKey(i)).Move after:=rng.Parent
            .GetByIndex(i).Copy Sheets(.GetKey(i)).Cells(1)
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

Hai alf ,jindon ,leith ross ,thank u very much for all your assistance...
I am very happy that one major problem of bringing other columns in to the sheets is solved..
As i told earlier ,two drawbacks (still exists) of these macro are
1) after shooting of names to other sheets,the names are displayed in all the sheets without serial numbers 1,2,3,ets
2) In the sheet "names" the macro does not have the control over which column it is considering to break the names,
by default ,it takes the first used column and it splits according to what is available in the first used column (for example if serial no is present in the first column,it splits these serial numbers and generates sheets like 1,2,3,4 etc)which is wrong ...It will be very fine it should always take from second column and also mainly cell B2 because it should leave space for title ,otherwise title name also go to some sheets...

Hai alf ,jindon ,leith ross ,thank u very much for all your assistance...
I am very happy that one major problem of bringing other columns in to the sheets is solved..
As i told earlier ,two drawbacks (still exists) of these macro are
1) after shooting of names to other sheets,the names are displayed in all the sheets without serial numbers 1,2,3,ets
2) In the sheet "names" the macro does not have the control over which column it is considering to break the names,
by default ,it takes the first used column and it splits according to what is available in the first used column (for example if serial no is present in the first column,it splits these serial numbers and generates sheets like 1,2,3,4 etc)which is wrong ...It will be very fine it should always take from second column and also mainly cell B2 because it should leave space for title ,otherwise title name also go to some sheets...
So only if the first column is entirely empty ,it takes the names in the second column so i am unable to add serial numbers or any other things in the first column...

Hai dear thank u very much ...It is very nice ..and 100 % perfect and I am really happy about it.I really thanks all the guys who helped me.. Last but least can this macro be further taken to the level where it ask us the column no when we run the macro,and if we give column no 4 it should break and create sheets based on the names in column 4 and so on

thank u soooo much .It is amazing

I am sorry to disturb u friend ,is there any way to create message box which asks us "Do u wnt to clr the old data?" depending upon yes or no ,it should either clear the existing data in all the sheets and replaces with the new one when the macro is run otherwise when the macro is run the date should be added to the existing data in the sheets.