删除工作簿中每个表的过滤器，VBA [英] Removing Filters for Each Table in a Workbook, VBA

问题描述

我正在尝试编写一个将在活动工作簿中的每个工作表中的每个表进行排序的工作，并重置所有过滤器，但是我似乎收到了一些对象错误。任何人都可以帮我指出为什么呢？请注意，每个表都是一个数据透视表。

  Sub ResetFilters（）
 Dim ws As Worksheet 
 Dim wb As Workbook 
 Dim listObj作为ListObjects 
 
对于每个ws在ActiveWorkbook.Worksheets 
对于每个listObj在ws 
与ActiveSheet.listObj.Sort.SortFields.Clear 
结束与
下一个listObj 
下一个ws 
 End Sub 
  

收到错误：Object doesn'

解决方案

假设你的意思是清除过滤器并删除排序，可以使用： / p>

  Sub ResetFilters（）
 Dim ws As Worksheet 
 Dim wb As Workbook 
 Dim listObj As ListObject 
 
对于每个ws在ActiveWorkbook.Worksheets 
对于每个listObj在ws.ListObjects 
如果listObj.ShowHeaders然后
 listObj.AutoFilter.ShowAllData 
 listObj.Sort.SortFields.Clear 
 End If 
下一个listObj 
下一个ws 
 End Sub 
  

I'm trying to write a piece that will sort through each table within each worksheet in an active workbook and reset all the filter, but it seems I'm getting a few object errors. Can anyone help me point out why that is? Note that each table is a pivot table.

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

Error received: "Object doesn't suppor this property or method" on Line 7.

解决方案

Assuming you mean clear filters and remove sorting, you can use:

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

这篇关于删除工作簿中每个表的过滤器，VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！