Solved

Excel VBA - Sort multiple tables when new items added

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

776 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