Find cell containing text in a Column beginning with a particular letter, then copy that row and paste it in a new sheet

[balrajsingh78]

I have a worksheet with a long list of customer names and an identifier along with some other information. I can easily sort them alphabetically, however, I need to separate all customer records (rows) for customers with names starting with 'A' in one worksheet, all those starting with 'B' in the second worksheet, and so on. So in brief, starting at row 2, the code needs to:

1) Copy all rows with customer name starting with A to the worksheet named 'A' (copy into rows 2 onwards)
2) repeat the same for every letter of the alphabet, copying the rows into separate sheets.

Please Note: Row 1 will have a header, so it needs to be ignored. Column A has the customer identifier. The column to be filtered is 'B'.
Can anyone please help?

#Others have asked similar questions, but none of them seemed help in with particular issue.

 

[My Aswer Is This]

Have you already created all these sheets.
Like sheet named "A'
Like sheet named "B"
So your saying if in column B you have "Bob" you want this row copied to a sheet named "B"
Correct?

 

[My Aswer Is This]

Assuming you have not made the sheets.
Use this script to make the sheets:

Sub Make_Sheets()
'Modified 4/7/2021  3:19:04 AM  EDT
Application.ScreenUpdating = False
Dim i As Long

For i = 1 To 26
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Mid(Cells(1, i).Address, 2, 1)
Next
Application.ScreenUpdating = True
End Sub

Use this script to copy data to sheets
This script should be run from the sheet with your data in column B

Sub Copy_Data_To_Sheet()
'Modified  4/7/2021  3:19:04 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long

For i = 2 To Lastrow
    Lastrowa = Sheets(Left(Cells(i, 2).Value, 1)).Cells(Rows.Count, "B").End(xlUp).Row + 1
    Rows(i).Copy Sheets(Left(Cells(i, 2).Value, 1)).Cells(Lastrowa, 1)
Next
Application.ScreenUpdating = True
End Sub

 

[balrajsingh78]

Mate this code works perfectly. No more painful copy and paste. Thanks a lot.

 

[My Aswer Is This]

Mate this code works perfectly. No more painful copy and paste. Thanks a lot.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.