过滤器在Excel 2003中可枢转 [英] Filter pivottable in Excel 2003

问题描述

如何在Excel 2003中使用VBA过滤数据透视表？

How do I filter a pivot table in Excel 2003 using VBA?

在Excel 2007中，我可以运行此宏，但是数据透视表未在XL 2​​003中实现。 p>

In Excel 2007 I can run this macro but PivotFilters are not implemented in XL 2003.

Dim ws As Worksheet: Set ws = Sheets("Sheet1")
ws.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
        Type:=xlSpecificDate, Value1:="26/01/2012"

更新：
我收到错误运行时错误1004。无法设置PivotItem类的Visible属性。

Update: I get an error "Run-time error '1004". Unable to set the Visible property of the PivotItem class.

Sub Filter（）
Dim PvtItem As PivotItem
Dim ws As Worksheet

Sub Filter() Dim PvtItem As PivotItem Dim ws As Worksheet

Set ws = Sheets("pivot")

'~~> Show All
For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
    PvtItem.Visible = True
Next

'~~> Show Only the relevant
For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
    If PvtItem.Value <> "26/01/2012" Then PvtItem.Visible = False  '<-- error here
Next

End Sub

http： //wikisend.com/download/426518/pivot.xls

推荐答案

要过滤VBA 2003中的PivotField，你必须设置/取消设置.Visible属性。这里是一个例子

To filter a PivotField in VBA 2003, you have to set/unset the .Visible Property. here is an example

Option Explicit

Sub Filter()
    Dim PvtItem As PivotItem
    Dim ws As Worksheet

    On Error GoTo Whoa1

    Set ws = Sheets("pivot")

    '~~> Show All
    For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
        PvtItem.Visible = True
    Next

    On Error GoTo Whoa2 '<~~ If no match found in Pivot

    '~~> Show Only the relevant
    For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
        If Format(PvtItem.Value, "DD/MM/YYYY") <> Format(Range("today"), "DD/MM/YYYY") Then
            PvtItem.Visible = False
        End If
    Next

    Exit Sub
Whoa1:
    MsgBox Err.Description
    Exit Sub
Whoa2:
    '~~> Show All
    For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
        PvtItem.Visible = True
    Next
End Sub

这篇关于过滤器在Excel 2003中可枢转的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！