如何让GateSort宏在Excel工作簿所有工作表/表格中生效？

问题说明 #

我录制了一个GateSort宏，原本仅在录制时的"Ramp"工作表生效，现在希望它能在工作簿的所有工作表的表格中运行。尝试替换固定工作表引用为动态引用、用变量修改后都没成功，不想手动逐个设置多级排序。

原始仅单工作表生效的宏 #

Sub GateSort()
'
' GateSort Macro
' Automatic sorting by Terminal > Gate > Subordinate value
'
' Keyboard Shortcut: Ctrl+Shift+G
'
    ActiveWorkbook.Worksheets("Ramp").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ramp").ListObjects("Table1").Sort.SortFields.Add2 _
        Key:=Range("Table1[Sort Gate Leading]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Ramp").ListObjects("Table1").Sort.SortFields.Add2 _
        Key:=Range("Table1[Sort Gate Number]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Ramp").ListObjects("Table1").Sort.SortFields.Add2 _
        Key:=Range("Table1[Sort Gate Trailing]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Ramp").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

我的尝试版本（未成功） #

Sub GateSort()
'
' GateSort Macro
' Automatic sorting by Terminal > Gate > Subordinate value
'
' Keyboard Shortcut: Ctrl+Shift+G
'
tName = ActiveCell.ListObject.Name

    ActiveWorkbook.ActiveSheet.ListObjects(tName).Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.ListObjects(tName).Sort.SortFields.Add2 _
        Key:=Range("tName[Sort Gate Leading]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.ActiveSheet.ListObjects(tName).Sort.SortFields.Add2 _
        Key:=Range("tName[Sort Gate Number]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.ActiveSheet.ListObjects(tName).Sort.SortFields.Add2 _
        Key:=Range("tName[Sort Gate Trailing]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.ListObjects(tName).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

修正后的宏方案 #

方案1：对当前激活单元格所在的表格排序

这个版本会自动识别你选中单元格所在的表格并排序，适合单个表格快速排序：

Sub GateSort_CurrentTable()
'
' GateSort Macro
' 按Terminal > Gate > Subordinate value自动排序
'
' 快捷键: Ctrl+Shift+G
'
    Dim tbl As ListObject
    
    ' 检查当前单元格是否在表格内
    On Error Resume Next
    Set tbl = ActiveCell.ListObject
    On Error GoTo 0
    
    If tbl Is Nothing Then
        MsgBox "请先选中表格内的任意单元格！", vbExclamation
        Exit Sub
    End If
    
    ' 清空原有排序规则
    tbl.Sort.SortFields.Clear
    
    ' 添加排序字段，直接通过表格列对象获取范围，避免引用错误
    tbl.Sort.SortFields.Add2 _
        Key:=tbl.ListColumns("Sort Gate Leading").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    tbl.Sort.SortFields.Add2 _
        Key:=tbl.ListColumns("Sort Gate Number").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    tbl.Sort.SortFields.Add2 _
        Key:=tbl.ListColumns("Sort Gate Trailing").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    ' 应用排序设置
    With tbl.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

方案2：遍历工作簿所有工作表的所有表格排序

这个版本会自动处理当前工作簿里所有工作表中的所有表格，一次性完成批量排序：

Sub GateSort_AllTablesInWorkbook()
'
' GateSort Macro
' 对工作簿中所有工作表的所有表格，按Terminal > Gate > Subordinate value自动排序
'
' 快捷键: Ctrl+Shift+G
'
    Dim ws As Worksheet
    Dim tbl As ListObject
    
    ' 遍历每个工作表
    For Each ws In ActiveWorkbook.Worksheets
        ' 遍历工作表内的每个表格
        For Each tbl In ws.ListObjects
            ' 清空原有排序规则
            tbl.Sort.SortFields.Clear
            
            ' 添加排序字段
            tbl.Sort.SortFields.Add2 _
                Key:=tbl.ListColumns("Sort Gate Leading").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            tbl.Sort.SortFields.Add2 _
                Key:=tbl.ListColumns("Sort Gate Number").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            tbl.Sort.SortFields.Add2 _
                Key:=tbl.ListColumns("Sort Gate Trailing").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            
            ' 应用排序设置
            With tbl.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        Next tbl
    Next ws
    
    MsgBox "所有表格排序完成！", vbInformation
End Sub

关键修正点 #

• 原尝试版本的错误：Range("tName[Sort Gate Leading]")中的tName是变量，但直接写在字符串里VBA不会解析，改用tbl.ListColumns("列名").Range直接从表格对象获取列范围，更可靠且避免字符串拼接错误
• 引入ListObject变量简化代码，避免重复冗长的引用
• 增加错误检查（方案1），防止用户未选中表格时出错
• 遍历版本通过双层循环自动处理所有表格，完全无需手动操作

内容的提问来源于stack exchange，提问作者BMax1985