当其他工作簿打开时,工作簿中的宏失败-运行时错误1004



宏在它是唯一打开的工作簿时工作正常,但当任何其他工作簿打开时,它会失败。我相信这是一个很容易解决的问题,但我想不出来。提前谢谢你。

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

相关内容

  • 没有找到相关文章

最新更新