宏以两个条件对多个表进行排序

本文介绍了宏以两个条件对多个表进行排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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