Hi Everyone,
I use the following code to copy and and sort information from one sheet to another.
The code is basically used to copy & sort names from specific sheets via command buttons. (It works great and it does the job) but I was asked to make it sort by last name, not the first name.
The cells that the code copies from are written like this: (All text contained within the same cell for each name... Names are written as follow
Example format text: (With names and last names written in the same cell)
John Smith
Jane Doe
Jack Frost
Jane Smith
Jill Tracy
Donald Duck
Napoleon Dynamite
The code currently copies and sorts the names based on the first latter of the cell's string...
... I am hoping there is a way to tell it to copy and sort by the first character "after the space, or first character from the second part of the text string".
Can any someone Please! Assist me with this?
Here is my current code:
Sub CommandButton1_Click()
'Operators - A Shift
Worksheets("Drill Call List").Unprotect Password:="drill"
Range("B15:B200").Value = ""
Worksheets("Drill Call List").Protect Password:="drill"
Dim rng As Range, item As Range
Dim v() As Variant
Dim i As Integer
Dim lastrow As Long
Dim x As Long
Set rng = Sheets("Operators").Range("I9:I38")
i = 0
For Each item In rng
With item
If .Value > 0 Then
ReDim Preserve v(i)
v(i) = .Value
i = i + 1
End If
End With
Next
With Sheets("Drill Call List")
.Unprotect Password:="drill"
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
If .Range("B16") = "" Then lastrow = 15
x = UBound(v) + 1
Worksheets("Operators").Range("I9:I38").Copy
Worksheets("Drill Call List").Range("B16").PasteSpecial -4163
.Range("B16").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
'Sort Copied Data
Range("B15:B54").Copy
ActiveSheet.Range("B16").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Sort key1:=Range("B16")
.Protect Password:="drill"
End With
End Sub
Any help with this would be greatly appreciated.
Thank you in advance.
I use the following code to copy and and sort information from one sheet to another.
The code is basically used to copy & sort names from specific sheets via command buttons. (It works great and it does the job) but I was asked to make it sort by last name, not the first name.
The cells that the code copies from are written like this: (All text contained within the same cell for each name... Names are written as follow

Example format text: (With names and last names written in the same cell)
John Smith
Jane Doe
Jack Frost
Jane Smith
Jill Tracy
Donald Duck
Napoleon Dynamite
The code currently copies and sorts the names based on the first latter of the cell's string...
... I am hoping there is a way to tell it to copy and sort by the first character "after the space, or first character from the second part of the text string".
Can any someone Please! Assist me with this?
Here is my current code:
Sub CommandButton1_Click()
'Operators - A Shift
Worksheets("Drill Call List").Unprotect Password:="drill"
Range("B15:B200").Value = ""
Worksheets("Drill Call List").Protect Password:="drill"
Dim rng As Range, item As Range
Dim v() As Variant
Dim i As Integer
Dim lastrow As Long
Dim x As Long
Set rng = Sheets("Operators").Range("I9:I38")
i = 0
For Each item In rng
With item
If .Value > 0 Then
ReDim Preserve v(i)
v(i) = .Value
i = i + 1
End If
End With
Next
With Sheets("Drill Call List")
.Unprotect Password:="drill"
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
If .Range("B16") = "" Then lastrow = 15
x = UBound(v) + 1
Worksheets("Operators").Range("I9:I38").Copy
Worksheets("Drill Call List").Range("B16").PasteSpecial -4163
.Range("B16").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
'Sort Copied Data
Range("B15:B54").Copy
ActiveSheet.Range("B16").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Sort key1:=Range("B16")
.Protect Password:="drill"
End With
End Sub
Any help with this would be greatly appreciated.
Thank you in advance.
Last edited: