Auto Sort from multiple list by Name

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
Office Version
  1. 2016
Platform
  1. Windows
hello All,

I have a list column A is First Name Column B is Last name column C I have


C2=
Code:
CONCATENATE(B2,","," ",A2)

Puts it in "Last name, First Name"

What im needing done on another sheet is to automatically sort when a name is added/deleted from the list in Alphabetical order based on Last name than if last name are the same By first name,

Any suggestion is greatly appreciated
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
The worksheet holding the names should be named 'Names'
The worksheet holding the sorted list should be named 'Sorted'
I am assuming your names list has a header row, sot it is not included in the sort


Code:
Option Explicit

'Put this event trigger sub on the code page of the sheet that holds the
'  3 columns being edited.  It will trigger when the sheet recalculates
'Recalculation will occur if in Automatic mode when the column C cell
'  with the formula is copied/dragged to a new cell

Private Sub Worksheet_Calculate()
    CopyAndSortNames
End Sub
Code:
'Add this Sub to a standard module

Option Explicit

Sub CopyAndSortNames()
    'Erase the 'Sorted' worksheet
    'Copy the name list on the 'Names' worksheet to the 'Sorted' worksheet
    'Sort by column C
    
    Dim lLastRow As Long
    
    Worksheets("Sorted").Cells.ClearContents
    With Worksheets("Names")
        lLastRow = .Cells(.Rows.Count, 3).End(xlUp).Row 'Last populated cell in column C
        .Range("A1:C" & lLastRow).Copy
    End With
    With Worksheets("Sorted")
        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("C1:C" & lLastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sorted").Sort
            .SetRange ActiveWorkbook.Worksheets("Sorted").Range("A1:C" & lLastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Select
        .Range("A1").Select
    
    End With
End Sub
 
Upvote 0
How about this shorter code.


Code:
Sub CopyAndSortNames()
  Dim sh2 As Worksheet
  Application.ScreenUpdating = False
  Set sh2 = Sheets("[COLOR=#0000ff]Sorted[/COLOR]")
  Sheets("[COLOR=#0000ff]Names[/COLOR]").Range("A:C").Copy
  sh2.Range("A1").PasteSpecial xlPasteValues
  sh2.Range("A:C").Sort key1:=sh2.Range("B1"), order1:=xlAscending, Header:=xlYes
  Application.CutCopyMode = False
End Sub
 
Upvote 0
It is indeed shorter, but be sure to clear the sorted worksheet before copying rows from names in case some rows were removed from the original list.
 
Upvote 0
It is indeed shorter, but be sure to clear the sorted worksheet before copying rows from names in case some rows were removed from the original list.

Thanks for the observation. It is not necessary to delete previously, since I am copying the entire columns. ;)
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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
Back
Top