Using vba code to sort multiple tables based on the same column

Question

I need vba code to help me sort multiple tables in the same sheet without merging them into one table. I have a range of data, from B14:V18, another from B21:V39, and another from B42:V108. I need to sort the data based on the values in column L. However, it needs to be dynamic, because the number of rows in each table could change on any given day. Using the normal sort function in excel just merges all of the data into one table, not keeping them seperate.

The columns will always be B:V, but the rows will be changing. As well, the next table will always be located 3 rows below the last cell of the previous one.

@SkipIntro, it's true, but I wanted to know how to do it, so selfishly took the fish for my own. — Doug Glancy
– Doug Glancy, Commented May 30, 2013 at 14:17
I need to sort the info in column L from biggest to smallest for each table, by the way. — user2426779
– user2426779, Commented May 30, 2013 at 14:21

Community · Accepted Answer · 2017-05-23 10:28:44Z

2

I realize this thread is dead but wanted to provide a likely solution to the OP's problem. I used Doug's macro and encountered the same problem the OP did. I simply added .SortFields.Clear before the .SortFields.Add line, which then caused the macro to run correctly. See my post here for my code using two sort criteria.

Sub SortTables()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet

Set ws = ActiveSheet
For Each lo In ws.ListObjects
    With lo.Sort
        .SortFields.Clear
        .SortFields.Add Key:=lo.ListColumns("test2").Range, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
Next lo
End Sub

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Nov 12, 2014 at 6:54

Joe

1071 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Doug Glancy · Accepted Answer · 2013-05-30 14:02:53Z

1

This will sort all the tables on the active worksheet by a column named "test3". It should work no matter how the tables are situated on the sheet:

Sub SortTables()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet

Set ws = ActiveSheet
For Each lo In ws.ListObjects
    With lo.Sort
        .SortFields.Add Key:=lo.ListColumns("test2").Range, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
Next lo
End Sub

Tested in Excel 2010

answered May 30, 2013 at 14:02

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

3 Comments

user2426779 Over a year ago

Doesn't seem to be working for some reason. Do I need to have a certain cell selected? I literally copied that code into excel and changed "test 2" to "Upside", which is the word in my L1. Would this make sense?

Doug Glancy Over a year ago

I think I may have misunderstood. Are these three actual Tables, or just areas on a sheet?

Doug Glancy Over a year ago

And is your version Excel 2007 or later? If so, it should work as you describe, sorting each table by the column with a header of "Upside."

Collectives™ on Stack Overflow

Using vba code to sort multiple tables based on the same column

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related