宏以两个条件对多个表进行排序 [英] Macro to sort multiple tables on two criteria

问题描述

我徒劳地尝试使以下宏正常运行.最终目标是一个宏，它将基于两个条件对多个表(在单个工作表上)进行排序，并且还将在任何活动的工作表上工作.我可以使用精确的表引用创建一个宏，但要寻求一些更敏捷的方法，以避免每个工作表都有一个宏.

I have tried in vain to get the below macro to run correctly. The end goal is a macro that will sort multiple tables (on a single worksheet) based on two criteria, and which will also work on whatever worksheet is active. I can make a macro with exact table references, but seek something more agile to avoid having a macro for each worksheet.

我的大部分代码来自

The bulk of my code draws from Doug Glancy's recommendation in this post, but it doesn't run properly (as the original poster noted before the thread died). The macro runs without errors but doesn't actually sort any of the data.

我认为折断的部分是Key:=lo.ListColumns("Name of table column").Range部分.我对该语法不太熟悉，无法对其进行故障排除.

I think the broken portion is the Key:=lo.ListColumns("Name of table column").Range part. I am not familiar enough with that syntax to troubleshoot it.

我感谢任何建议或替代方案！另外，让我知道我的要求是否完全不清楚.

I appreciate any suggestions or alternatives! Also, let me know if my requirements are unclear at all.

Sub CustomSort()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet

Set ws = ActiveSheet
For Each lo In ws.ListObjects
    With lo.Sort
        .SortFields.Add Key:=lo.ListColumns("Status").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=lo.ListColumns("Inventory Number").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .Apply
    End With
Next lo
End Sub

推荐答案

感谢TomDillinger提示我仔细研究宏的行为.我意识到宏有时会排序，尽管很少正确.我只是在.SortFields.Add行之前添加了.SortFields.Clear，它清除了所有现有的排序以重新开始，因此可以说宏中的sort命令.这是功能齐全的宏:

Thanks to TomDillinger for prompting me to take a closer look at the macro's behavior. I realized the macro was sorting some of the time, although rarely correctly. I simply added .SortFields.Clear before the .SortFields.Add lines, which clears any existing sorting for a fresh start so to speak for the sort commands in the macro. Here is the fully functioning macro:

Sub CustomSort()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet

Set ws = ActiveSheet
For Each lo In ws.ListObjects
    With lo.Sort
        .SortFields.Clear
        .SortFields.Add Key:=lo.ListColumns("Status").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=lo.ListColumns("Inventory Number").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .Apply
    End With
Next lo
End Sub

这篇关于宏以两个条件对多个表进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！