Solved

Excel VBA - Sort multiple tables when new items added

Posted on 2015-01-07
3
305 Views
Last Modified: 2015-01-07
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
0
Comment
Question by:xllvr
  • 2
3 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:xllvr
Comment Utility
This is excellent!  Thanks so much, Martin!
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now