Split data in one worksheets into multiple sheets?

Try this code

Option Explicit

Sub split_data()
Dim i As Long, lrow As Long
Dim sname As String

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    If Worksheets(i).Name <> "All" Then
        lrow = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
        If lrow > 1 Then Worksheets(i).Range("A2:O" & lrow).Delete
    End If
Next i

With Worksheets("All")
    lrow = .Range("B" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        sname = .Range("C" & i).Value
        .Range("B" & i & ":P" & i).Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next i
End With

Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Let me know if you need a small addition to the code which checks if the sheetname exists and adds one if it doesnt.

Yes, if you delete / add someone, you need to rerun the code. It will clear all the sup sheets and re-populate.

The updated code is here -

Option Explicit

Sub split_data()
Dim i As Long, lrow As Long
Dim sname As String

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    If Worksheets(i).Name <> "All" Then
        lrow = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
        If lrow > 1 Then Worksheets(i).Range("A2:O" & lrow).Delete
    End If
Next i

With Worksheets("All")
    lrow = .Range("B" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        sname = .Range("C" & i).Value
        If Not Evaluate("ISREF('" & sname & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sname
            .Range("B1:P1").Copy Worksheets(sname).Range("A1")
        End If
        .Range("B" & i & ":P" & i).Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        worksheets(sname).cells.entirecolumn.autofit
    Next i
End With

Application.ScreenUpdating = True

End Sub

I have edited the code above. I had some extra text in there by mistake.

Please try and let me know.

Which line is highlighted in yellow when you get the error?

Is your main worksheet named "All" as it was in the summary file? When you get a subscript out of range error, its all got to do with the sheetname.

The macro will clear the contents of the existing sheets and re-populate with data.

If the sheet name does not exist, it will create it for you.

I have added the code line to autofit the columns of each sheet.