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

balrajsingh78

New Member
Joined
Apr 5, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,911
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,911
Office Version
  1. 2013
Platform
  1. Windows
Assuming you have not made the sheets.
Use this script to make the sheets:

VBA Code:
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
VBA Code:
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

New Member
Joined
Apr 5, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
Mate this code works perfectly. No more painful copy and paste. Thanks a lot.
 

Forum statistics

Threads
1,143,621
Messages
5,719,795
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top