Solved

Excel VBA - Sort multiple tables when new items added

Posted on 2015-01-07
3
313 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 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40536538
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
ID: 40536571
This is excellent!  Thanks so much, Martin!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40536611
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create Excel formula on dynamic data 5 35
Easy Excel formula needed 4 27
Sum iF  based on a null cell 11 29
Error in formula not increasing value 2 15
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

896 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

13 Experts available now in Live!

Get 1:1 Help Now