VBA to sort all tables on worksheet

DJ'sGiGi

Board Regular
Joined
May 25, 2007
Messages
92
I have the following macro that's supposed to sort all the tables on the worksheet named "Lists" but I keep getting an error at the line "For Each lo In ws". Can some please show me the error in my code? I'm open to a completely different code if it serves the purpose. Not sure it matters but there are currently 7 dynamic tables that have defined names. Thanks in advance for the help. Here's the code:

Sub Sort()

Dim lo As ListObject
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Lists")
ws.Activate

For Each lo In ws
With lo.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
Next lo
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
that for each needs the collection to be named. what I mean is,
for each lo in ws.listobjects
I haven't checked if they are called listobjects

just like
for each ws in activeworkbook.worksheets
for each pt on ws.pivottables
 
Upvote 0

Forum statistics

Threads
1,215,025
Messages
6,122,734
Members
449,094
Latest member
dsharae57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top