Custom Sorting with variables in VBA

Question

The macro below is a custom sort that I recorded. I replaced the actual table name with variable so I can run this for any other table name. I didn't want it to be specific to one name. When I run the macro I get a:

Run-time error '1004': the item with the specified name wasn't found

When I click the help button it sends me to Excel help menu. Could you guys let me know what I am missing? And if possible is there a way to shorten up my code. I know recordings can be long.

**Note all of this is in a table

Dim sh As Worksheet
Dim TableName As String

 Set sh = ActiveSheet
 TableName = sh.Name


    ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort. _
      SortFields.Clear
    ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort. _
      SortFields.Add Key:=Range(TableName, [BEVEL]), SortOn:=xlSortOnValues,
      Order:=xlAscending, _
      CustomOrder:="BEVEL_YES_MITER,BEVEL_NO_RADIUS", DataOption:=xlSortNormal _

    ActiveWorkbook.Worksheets(TableName).ListObjects("TableName").sort. _
      SortFields.Add Key:=Range(TableName, [MATERIAL]), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort. _
      SortFields.Add Key:=Range(TableName, [Length]), SortOn:=xlSortOnValues, _
      Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
     End With
Selection.AutoFilter
End Sub

Not sure if I am using the word variables correctly.

Ship72
– Ship72

2013-10-23 15:23:01 +00:00
Commented Oct 23, 2013 at 15:23 — Ship72
– Ship72, Commented Oct 23, 2013 at 15:23

RBarryYoung · Accepted Answer · 2013-10-23 18:49:04Z

1

First, I notice that though most of your table references are like this ..ListObjects(TableName).., one of them looks like this ..ListObjects("TableName").., so I'm guessing that's where the error is coming from.

However, in general I'd recommend putting not just the table-name into a variable, but also the table itself, like this:

Dim sh As Worksheet
Dim TableName As String
Dim theTable As ListObject

 Set sh = ActiveSheet
 TableName = sh.Name
 Set theTable = ActiveWorkbook.Worksheets(TableName).ListObjects(TableName)

    theTable.sort.SortFields.Clear
    theTable.sort.SortFields.Add _
      Key:=Range(TableName & "[BEVEL]"), SortOn:=xlSortOnValues,
      Order:=xlAscending, _
      CustomOrder:="BEVEL_YES_MITER,BEVEL_NO_RADIUS", DataOption:=xlSortNormal 

    theTable.sort.SortFields.Add 
      Key:=Range(TableName & "[MATERIAL]"), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    theTable.sort.SortFields.Add  _
      Key:=Range(TableName & "[Length]"), SortOn:=xlSortOnValues, _
      Order:=xlDescending, DataOption:=xlSortNormal
    With theTable.sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
     End With
Selection.AutoFilter
End Sub

(Note: corrected the line breaks)

(Note: corrected Range table-column name parameters)

edited Oct 23, 2013 at 18:49

answered Oct 23, 2013 at 15:41

RBarryYoung

57.1k15 gold badges99 silver badges143 bronze badges

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

7 Comments

Ship72 Over a year ago

For some reason it doesnt like the following: theTable.sort. _ SortFields.Add Key:=Range(TableName, [BEVEL]), SortOn:=xlSortOnValues, Order:=xlAscending, _ CustomOrder:="BEVEL_YES_MITER,BEVEL_NO_RADIUS", DataOption:=xlSortNormal _

RBarryYoung Over a year ago

Oh, right. You cannot put a space or line break(" _ ") before or after dots (".") in a reference path. You can only put them between operands, etc. Basically wherever a space would normally go.

RBarryYoung Over a year ago

@user2792786 OK, I have corrected the line breaks in my code above.

Ship72 Over a year ago

It still doesnt like it bud:

theTable.sort.SortFields.Add _       Key:=Range(TableName, [BEVEL]), SortOn:=xlSortOnValues, _       Order:=xlAscending, CustomOrder:="BEVEL_YES_MITER,BEVEL_NO_RADIUS", DataOption:=xlSortNormal

Ship72 Over a year ago

The item with the specified name wasn't found

|

Collectives™ on Stack Overflow

Custom Sorting with variables in VBA

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related