PDA

View Full Version : Problem sorting each table in workbook



Pichon
09-26-2018, 06:46 PM
I'm banging my head against the wall trying to sort each table in my workbook alphabetically by first column. My code is as follows:



Sub alphabetize()
Dim sht As Worksheet
Dim tbl As ListObject
Dim i As Integer, x As Integer

Application.ScreenUpdating = False

For i = 1 To ThisWorkbook.Worksheets.Count
Set sht = ThisWorkbook.Worksheets(i)
If sht.Name <> "CLDatabase" And sht.Name <> "Admin" And sht.Name <> "Staging" Then
For x = 1 To sht.ListObjects.Count
Set tbl = sht.ListObjects(x)
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=Range(tbl.ListColumns(1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.Apply
End With
Next x
End If
Next i
End Sub


It runs fine through some worksheets but stops on one sheet in particular and gives me a 1004 Method Range of object global failed error on the .SortFields.Add line.

Any ideas how to fix this?