Excel VBA - Sort multiple tables when new items added

Hi Experts,

I have a sheet of single column tables that are ultimately used in data validation on another sheet in the workbook.  I'd like help with adding code to the workbook which would sort any/all of the tables if the user adds a new entry.  Workbook attached.

Appreciate any help you can offer!  Thanks!
EE-TableSortTest.xlsm
LVL 1
xllvrAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Add this code to the LISTS sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lo As ListObject

For Each lo In Sheets("Lists").ListObjects
    SortTable lo
Next

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
Private Sub SortTable(lo As ListObject)
   ActiveWorkbook.Worksheets("LISTS").ListObjects(lo.Name).Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("LISTS").ListObjects(lo.Name).Sort.SortFields _
        .Add Key:=Range(GetRange(lo.Range.Columns(1).Column)), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LISTS").ListObjects(lo.Name).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Private Function GetRange(col As Integer) As String
    GetRange = Split(Cells(1, col).Address, "$")(1) & ":" & Split(Cells(1, col).Address, "$")(1)
End Function

Open in new window

0
 
xllvrAuthor Commented:
This is excellent!  Thanks so much, Martin!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.