VBA—“错误1004:无法获取PivotField类的CurrentPage属性". [英] VBA—"Error 1004: Unable to get the CurrentPage property of the PivotField class"

问题描述

我有一个Excel工作簿，其中包含多个工作表，每个工作表上都有多个数据透视表.我试图遍历每个数据透视表，并将其报告功能"过滤器更改为从列表框中选择的用户指定值.所有数据透视表均具有报告功能"过滤器.

I have an Excel workbook containing multiple worksheets with multiple pivot tables on each worksheet. I'm attempting to loop through every pivot table and change its 'Report Function' filter to a user-specified value selected from a List Box. All the pivot tables have the "Report Function" filter.

我大约有50个数据透视表，并且代码成功地更改了除8个数据透视表之外的所有数据的过滤器.这8个枢纽分析表的筛选器不会改变；而是，返回错误错误1004:无法获取PivotField类的CurrentPage属性".

I have around 50 pivot tables, and the code successfully changes the filters for all but around 8 pivots. Those 8 pivot tables' filters don't change; instead, the error "Error 1004: Unable to get the CurrentPage property of the PivotField class" is returned.

为什么对于少量数据透视表，此代码返回此错误?

wHY is this code returning this error for a small number of pivot tables?

奇怪的是，似乎完全相同的数据透视表正在返回此错误.

Strangely, it seems to be the exact same pivot tables that are returning this error.

Sub ChangePivotFilter(str As String)

Dim ws As Excel.Worksheet
Dim aWB As Excel.Workbook
Dim myPivot As Excel.PivotTable
Dim myPivotField As Excel.PivotField

Set aWB = ActiveWorkbook
For Each ws In aWB.Worksheets

    For Each myPivot In ws.PivotTables
        Set myPivotField = Nothing

        On Error Resume Next
        Set myPivotField = myPivot.PivotFields("Report Function")
        myPivotField.CurrentPage = str
    Next myPivot
Next ws

End Sub

非常感谢您的帮助！

推荐答案

如果数据透视表字段不在其数据透视表的页面"(即过滤器")中，则分配或读取CurrentPage将会失败:

Assigning to, or reading from CurrentPage will fail if the PivotField isn't physically in "page" (i.e. a "Filter") on its PivotTable:

您可以通过将PivotField的Orientation属性设置为xlPageField来以编程方式将其发送到页面"，如下所示:

You can programmatically send a PivotField to "page" by setting its Orientation property to xlPageField, as follows:

myPivotField.Orientation = xlPageField

这篇关于VBA—“错误1004:无法获取PivotField类的CurrentPage属性".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！