Filter Range Copy Paste the Value and Create new Sheets

LearningExcelVBA 

You should always post a relevant example instead of one simplified too far.

| is allowed in a sheet name, but the following characters aren't: \ , / , * , ? , : , [ , ].

To get rid of those:

Sub SplitData()
    Dim src As Worksheet
    Dim trg As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim col As New Collection
    Dim itm As Variant
    Dim c As Variant
    Application.ScreenUpdating = False
    Set src=Worksheets("Sheet1")
    src.AutoFilterMode = False
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
    On Error Resume Next
    For r = 2 To lastRow
        itm = CStr(src.Range("A" & r).Value)
        col.Add Item:=itm, Key:=itm
    Next r
    On Error GoTo 0
    For Each itm In col
        For Each c In Array("\", "/", "*", "?", ":", "[", "]")
            itm = Replace(itm, c, "_")
        Next c
        Set trg = Nothing
        On Error Resume Next
        Set trg = Worksheets(itm)
        On Error GoTo 0
        If trg Is Nothing Then
            Set trg = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            trg.Name = itm
        Else
            trg.UsedRange.ClearContents
        End If
        src.UsedRange.AutoFilter Field:=1, Criteria1:=itm
        src.UsedRange.Copy Destination:=trg.Range("A1")
    Next itm
    src.AutoFilterMode = False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub