筛选枢纽分析表1004错误 [英] Filter pivot table 1004 error

问题描述

Holla！我正在尝试使用宏过滤数据透视表.代码如下:

Holla! I am trying to filter pivot table using macros. The code as follows:

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

但是我遇到错误1004

However i am getting error 1004

无法获取PivotTable类的PivotFields属性

Unable to get the PivotFields property of the PivotTable class

屏幕截图:

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

I've browsed a lot of forums and the only answer i could found was that pivot table or field with such name does not exist, but mine does. I even tried to use other fields that i have and it still gives me this error. I use MS office 2013

推荐答案

出现错误1004的原因是您没有PivotFields("Transaction status")或没有PivotItems("success").

Error 1004 is achieved because you either do not have PivotFields("Transaction status") or it does not have PivotItems("success").

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

Whenever you are working with PivotTables, PivotFields and PivotItems in VBA it is really useful to declare them as such. If you do so, you get access to them as collections and you can iterate through the objects of the collection.

在您的情况下，类似这样的内容将使您可以操纵.Visible属性:

In your case, something like this will allow you to manipulate the .Visible property:

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

修改: 这是循环遍历关键字段的方法

This is how to loop through the pivot fields

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

编辑2，从OP截屏:

替换:

PivotFields("Transaction status")

使用:

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

这篇关于筛选枢纽分析表1004错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！