VBA Table Sort Error

Question

Hello,

I'm trying to get a table that I created and named in my VBA code to sort alphabetically. However when the code reaches the point to call out the range and define the sort parameters (emboldened in the code), it keeps popping up with error code 400. I'm not sure what the issue is, in part because I used a similar chunk of code in macro that works perfectly fine. Any help would be appreciated.

    Dim itemtable As Excel.ListObject

    ActiveSheet.UsedRange.Select

    Set itemtable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)

    itemtable.Name = "Itemized_Parts_Table"

    With itemtable.Sort

        .SortFields.Clear

        'Issue is here'

        .SortFields.Add Key:=Range("Itemized_Parts_Table[Level 1 Install]"), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

Answer 1

Okay,

assuming that 'ListObject' workbook named: abcd.xlsx  is closed

in path "c:\Users\Tasos\Desktop\abcd.xlsx"

and data in Sheet1 (first sheet tab)

try this code...

[edit...]

Sub macro_01()

'Apr 12, 2016

Dim wb1 As Workbook

Application.ScreenUpdating = False

Set wb1 = Workbooks.Open("c:\Users\Tasos\Desktop\abcd.xlsx")

'start sort ###

With wb1.Sheets(1).ListObjects("**Table1").**Sort.SortFields

.Clear

.Add Key:=Range("Table1[Header4]"), SortOn:=xlSortOnValues, _

Order:=xlAscending, DataOption:=xlSortNormal

End With

With wb1.Sheets(1).ListObjects("Table1").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

'end sort ###

wb1.Save

wb1.Close False

Application.ScreenUpdating = True

MsgBox "done"

End Sub

Answer 2

Thanks for the idea, but the error is still coming up. I'm wondering if the issue is being caused by the fact that I am trying to run the sort command from a separate workbook from the one that is doing the sorting...

Answer 3

Hi,

try this...

data in activesheet,

list object name is Table_1

sort by column D (4th column)

header's name is Header4

Sub Sort_ByColumn_D_ListObject()

'Apr 12, 2016

With ActiveSheet.ListObjects("Table_1").Sort.SortFields

.Clear

.Add Key:=Range("Table_1[Header4]"), SortOn:=xlSortOnValues, _

Order:=xlAscending, DataOption:=xlSortNormal

End With

'

With ActiveSheet.ListObjects("Table_1").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

xxxxxxxxxxxxxxxxxxxxxxx

also,

Sort by two columns

(first D, next  B)

Sub Sort_ByColumns_D_B_ListObject()

'Apr 12, 2016

With ActiveSheet.ListObjects("Table_1").Sort.SortFields

.Clear

.Add Key:=Range("Table_1[Header4]"), SortOn:=xlSortOnValues, _

Order:=xlAscending, DataOption:=xlSortNormal

.Add Key:=Range("Table_1[Header2]"), SortOn:=xlSortOnValues, _

Order:=xlAscending, DataOption:=xlSortNormal

End With

'

With ActiveSheet.ListObjects("Table_1").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub