Using vba code to sort multiple tables based on th

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.

Answer 1

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

Answer 2

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