I have revamped the codes as follows for you:-
- The serial number (unique IDs) will be created automatically once you enter a value in Column A without having to click on a button. This includes addition of a dash.
- I have removed the Sort function and added a Filter function instead. This way, as your list grows, you can place a value from Column A in the Filter box (cell D1) then click on "GO" and all data for the selected value will be filtered and displayed for you to analyse.
- I have added a "Filter Off" button for once you are done analysing.
So, all the following codes go into a standard module:-
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & lr)
.Formula = "=Left(A2,2) & "" -""&Row()+1000"
Dim rng As Range
For Each rng In Range("B2", Range("B" & Rows.Count).End(xlUp))
On Error Resume Next
rng = StrConv(rng.Text, vbUpperCase)
On Error GoTo 0
Dim fSrch As String
fSrch = [D1].Value
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, fSrch
[D1].Value = "Filter"
The following code needs to be placed in the worksheet module:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
This code is the one which allows you to type a value in Column A (Nike, Adidas etc...) and then when you click away (or press enter or down arrow) the IDs will be created in Column B.
To implement this sheet code, right click on the Sheet1 tab (or whatever your sheet is named), select "view code" from the menu that appears and then, in the big white field that then appears, paste the code.
The subroutine named "UpperCase" is probably not really necessary but.....................
In your actual workbook, the subroutine named "Filter" needs to be assigned to the "GO" button and the subroutine named "FilterOff" needs to be assigned to the "Filter Off" button.
Following is the link to my updated test workbook:-
Type a value in Column A at the bottom of the existing values then click away (or press enter or down arrow) to see the codes at work.
Make sure that you test all the above in a copy of your actual workbook first.
I hope that this helps.