excel - 刷新 Excel 表格过滤器和使用 VBA 排序时出错

Question

在我的工作簿中，我有一个简单的工作表(“Master”)，其中包含一个表格(“Table1”)和两个单元格的命名范围(“filter_input”)。该表包括条件格式，可突出显示包含“filter_input”范围内任一单元格值的任何行。然后对表格进行排序以将突出显示的行放在表格的顶部。手动刷新表格排序(Ctrl-Alt-L)工作得很好。

为了自动化这个，我设置了 Worksheet_change 来识别“filter_input”范围内的变化，如果是这样，表格的过滤和排序应该自动刷新。我有以下情况:

Private Sub Worksheet_Change(ByVal target As Range)

Set isect_filter = Application.Intersect(target, Range("filter_input"))

If isect_filter Is Nothing Then

Else

    ActiveWorkbook.Worksheets("Master").ListObjects("Table1").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Master").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With

    End If

End Sub

此代码在过去 12 个月内一直运行没有问题，但在今天打开工作簿时，触发代码导致运行时错误:

Method 'AutoFilter' of object 'ListObject' failed.

高亮线:

ActiveWorkbook.Worksheets("Master").ListObjects("Table1").AutoFilter.ApplyFilter

我没有更改代码或文档中的任何内容，我能想到的唯一更改是我最近收到了一台新笔记本电脑并正在运行 Office 365。我很难相信这会是问题，但我不能看看还有什么变化。我在不同的工作簿中也有几乎相同的代码，这也给了我与今天相同的错误。

如果我注释掉该行，我会收到类似的错误，指的是以下行和方法“排序”。我检查了其他刷新排序的方法，但它们似乎都没有工作。

我什至尝试使用宏记录器来记录重新应用过滤器(或 Ctrl-Alt-L)的操作，但是它记录的代码似乎错误:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C6:C7").DataFields
    Range("C7").
    ColorIndex.("Table1")..SubAddress
    With ThemeColor.("Master").("Table1").
        .Count = xlYes
        .ActiveSheet = False
        . = xlTopToBottom
        . = xlPinYin
        .
    End With
End Sub

编辑 26/07/2018 - Record Macro 生成的第二次粘贴代码

这是 Record Macro 生成的代码的另一个粘贴 - 这次没有单元格选择或任何其他内容，除了按 Ctrl-Alt-L 刷新表格。我还通过功能区菜单(主页选项卡 > 编辑 > 排序和筛选 > 重新应用)运行了相同的功能，并且生成的代码是相同的。

Sub Macro3()
'
' Macro3 Macro
'

'
    ColorIndex.("Table1")..SubAddress
    With ThemeColor.("Master").("Table1").
        .Count = xlYes
        .ActiveSheet = False
        . = xlTopToBottom
        . = xlPinYin
        .
    End With
End Sub

Answer 1

好吧，我找到了一个解决方案——尽管我不确定为什么我的原始代码不起作用，尤其是在它之前运行良好的情况下。

我没有对在线定义的表执行操作，而是首先将表定义为 ListObject 变量并执行相同的操作。

所以原代码:

ActiveWorkbook.Worksheets("Master").ListObjects("Table1").AutoFilter.ApplyFilter
With ActiveWorkbook.Worksheets("Master").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

变成:

Dim ActiveTable As ListObject
Set ActiveTable = ActiveWorkbook.Worksheets("Master").ListObjects("Table1")

ActiveTable.AutoFilter.ApplyFilter
With ActiveTable.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

以下是完整的完整代码:

Private Sub Worksheet_Change(ByVal target As Range)

    Set isect_filter = Application.Intersect(target, Range("filter_input"))

    If isect_filter Is Nothing Then

    Else
        Dim ActiveTable As ListObject
        Set ActiveTable = ActiveWorkbook.Worksheets("Master").ListObjects("Table1")

        ActiveTable.AutoFilter.ApplyFilter
        With ActiveTable.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If

End Sub

如果有人能向我解释这背后的逻辑，我将不胜感激。

谢谢