过滤数据透视表1004错误-Java 学习之路

呼啦！我试图使用宏过滤透视表 . 代码如下：

Dim Pi As PivotItem
With ActiveWorkbook.Worksheets("TimeToFreq").PivotTables("timetofreq").PivotFields("Transaction status")
    .ClearAllFilters
    .PivotItems("success").Visible = True       
End With

但是我收到错误1004

无法获取数据透视表类的PivotFields属性

截图：

enter image description here

我浏览了很多论坛，我能找到的唯一答案是不存在具有此类名称的数据透视表或字段，但我的确如此 . 我甚至试图使用我拥有的其他字段，它仍然给我这个错误 . 我使用MS office 2013

3 回答

错误 1004 已实现，因为您没有 PivotFields("Transaction status") 或它没有 PivotItems("success") .

每当您在VBA中使用 PivotTables ， PivotFields 和 PivotItems 时，将它们声明为非常有用 . 如果这样做，您可以将它们作为集合进行访问，并且可以遍历集合的对象 .

在你的情况下，这样的东西将允许你操纵 .Visible 属性：

Public Sub TestMe()

    Dim pi As PivotItem
    Dim pt As PivotTable
    Dim pf As PivotField

    Set pt = ActiveWorkbook.Worksheets("TimeToFreq").PivotTables("timetofreq")
    Set pf = pt.PivotFields("Transaction status")

    pt.ClearAllFilters

    For Each pi In pf.PivotItems
        Debug.Print pi.Name

        If pi.Name = "success" Then
            pi.Visible = True
        End If
    Next pi

End Sub

Edit: 这是循环透视字段的方法

Set pt = ActiveWorkbook.Worksheets("TimeToFreq").PivotTables("timetofreq")
For Each pf In pt.PivotFields
    Debug.Print ">" & pf.Name & "<"
Next pf

Edit 2, after screenshot from OP ：

更换：

PivotFields("Transaction status")

附：

PivotFields("[Data_New].[Transaction status].[Transaction status]")

回复于 2024-04-28T18:58:09+08:00

过滤一个项目上的字段的最快方法是使其成为PageField，并使用像Deepak这样的代码 . 否则，您需要使用我在Pivotfields multiple filter发布的代码，以确保至少有一个项目始终可见 .

Sub FilterPivot()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Long
Dim vItem As Variant
Dim vItems As Variant

Set pt = ActiveSheet.PivotTables("SomePivotTableName") 'Replace with your PivotTable name 
Set pf = pt.PivotFields("SomePivotTableName") 'Replace with your PivotField name 

vItems = Array("This", "That", "SomethingElse") 'Your terms here
' If filtering on just one item, use vItems = Array("This")

pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed

With pf

    'At least one item must remain visible in the PivotTable at all times, so make the first
    'item visible, and at the end of the routine, check if it actually  *should* be visible        
    .PivotItems(1).Visible = True

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .PivotItems.Count
        If .PivotItems(i).Visible Then .PivotItems(i).Visible = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vItems
        .PivotItems(vItem).Visible = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the items of interest
    On Error Resume Next
    If InStr(UCase(Join(vItems, "|")), UCase(.PivotItems(1))) = 0 Then .PivotItems(1).Visible = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Pivot, so I have cleared the filter"
    End If
    On Error GoTo 0

End With

pt.ManualUpdate = False

End Sub

回复于 2024-04-28T18:58:09+08:00

-1

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("TimeToFreq")
ws.PivotTables("timetofreq").PivotFields("Transaction status").CurrentPage = "Success"

回复于 2024-04-28T18:58:09+08:00

过滤数据透视表1004错误

3 回答

相关问题