VBA: Sort a Excel Table (
宏在它是唯一打开的工作簿时工作正常,但当任何其他工作簿打开时,它会失败。我相信这是一个很容易解决的问题,但我想不出来。提前谢谢你。
Sub Sort_Leaders()
Dim wb As Workbook: Set wb = ThisWorkbook
wb.Worksheets("TABLE").ListObjects("Table1").Sort.SortFields.Clear
wb.Worksheets("TABLE").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With wb.Worksheets("TABLE").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Call test
End Sub
VBA: Sort a Excel Table (ListObject
)
快速("Easy")修复
使用
Key:=wb.Worksheets("TABLE").Range("Table1[[#All],[Rank]]")
不是
Range("Table1[[#All],[Rank]]")
是
的缩写ActiveSheet.Range("Table1[[#All],[Rank]]")
另一种方法:引用对象
Sub SortLeaders()
Const wsName As String = "TABLE"
Const tblName As String = "Table1"
Const lcName As String = "Rank"
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
Dim tbl As ListObject: Set tbl = ws.ListObjects(tblName)
Dim lc As ListColumn: Set lc = tbl.ListColumns(lcName)
With tbl.Sort
With .SortFields
If .Count > 0 Then .Clear ' or just '.Clear'
.Add lc.Range, xlSortOnValues, xlAscending, , xlSortNormal
End With
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Call Test ' irrelevant
End Sub