Hi everyone,

I'm still new with macro programming,, i have this code for transferring data to different worksheet,,but it seems that it does not do what i want,,can you help me edit this code
Supposed to be Name starts with letter B will be transfer on the sheet2 and name starts with letter C will be on sheet3 and so on up to letter Z,, with all the rows within it,, ex. Name, Age , ***

Code:

Sub test()
Dim r As Range, ws As Worksheet, rng As Range
With ThisWorkbook
With .Sheets("sheet1")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With
For Each r In rng
If UCase(r.Value) Like "[B-Z]*" Then
On Error Resume Next
Set ws = .Sheets(Left$(r.Value, 1))
On Error GoTo 0
If ws Is Nothing Then
Set ws = ThisWorkbook.Sheets.Add
ws.Name = Left$(r.Value, 1)
End If
With r.EntireRow
ws.Rows(ws.Cells(Rows.Count, 1).End(xlUp)(2).Row).Value = .Value
.ClearContents
End With
End If
Next
With .Sheets("sheet1")
With .Range("a2", .Range("a" & Rows.Count).End(xlUp))
On Error Resume Next
.SpcialCells(4).EntireRow.Delete
End With
End With
End With
End Sub