范围已转向表格，宏将无法运行-Rangehasturnedtoatableandmacrowon'trun

I have had to change the formatting on a spreadsheet and the range referred to below is now a table.

我不得不更改电子表格上的格式,下面提到的范围现在是一个表格。

Since changing this, my macro reports "Method 'Sort' of object 'Range' failed".

自更改此设置后,我的宏报告“方法'排序'对象'范围'失败”。

I can't name the table directly as the code cycles through all sheets in a spreadsheet (except excluded) and new sheets will be added that I won't know the table name/number for.

我无法直接命名表格,因为代码循环显示电子表格中的所有工作表(除外),并且将添加新工作表,我将不知道表名称/编号。

How do I select the table (location B72:L86) and sort it by column I?

如何选择表格(位置B72:L86)并按列I排序?

For Each wsSheet In ThisWorkbook.Worksheets
   Select Case wsSheet.Name
    Case "Affiliates", "New Report", "Pasted Report", "New Month Or Client", "Set Up Data"
        'Do nothing.

    Case Else
        With wsSheet
    .Range("B72:L86").Sort Key1:=.Range("I72"), Order1:=xlDescending
        End With
End Select

1 个解决方案

Dim lo as ListObject
With wsSheet
    For Each lo in .ListObjects
    lo.Sort.SortFields.Add Key:=Range(lo.Name & _
    "[[#All],[" & .Range("I72").value & "]]"), SortOn:=xlSortOnValues, Order:= xlDescending
    With lo.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Next    
End With