excel - 删除工作簿中每个数据透视表的筛选器

Question

我正在尝试对事件工作簿中每个工作表中的每个表格进行排序并重置所有过滤器。

请注意，每个表都是一个数据透视表。

Sub ResetFilters()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim listObj  As ListObjects

    For Each ws In ActiveWorkbook.Worksheets
        For Each listObj In ws
            With ActiveSheet.listObj.Sort.SortFields.Clear
        End With
        Next listObj
    Next ws
    End Sub

收到错误:第 7 行“对象不支持此属性或方法”。

Answer 1

假设您的意思是清除过滤器并删除排序，您可以使用:

Sub ResetFilters()
    Dim ws                    As Worksheet
    Dim wb                    As Workbook
    Dim listObj               As ListObject

    For Each ws In ActiveWorkbook.Worksheets
        For Each listObj In ws.ListObjects
            If listObj.ShowHeaders Then
                listObj.AutoFilter.ShowAllData
                listObj.Sort.SortFields.Clear
            End If
        Next listObj
    Next ws
End Sub