使用VBA更改数据透视表过滤器

I have a pivot table which contains the "CoB Date" field as shown.
I am trying to create a macro which automatically changes the date as per the user input.
I've written the following macro code. But it shows the error:

我有一个数据透视表，其中包含“CoB Date”字段，如图所示。我正在尝试创建一个宏，根据用户输入自动更改日期。我写了以下宏代码。但它显示错误：

Unable to get PivotFields property of the PivotTable class

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

Can any one help me with this?
Note: Assume that Date Format is not an issue

谁能帮我这个？注意：假设日期格式不是问题

Code:

码：

Sub My_macro()
    Dim num as String
    num = InputBox(Prompt:="Date", Title:="ENTER DATE")
    Sheets("Sheet1").PivotTables("PivotTable1") _
        .PivotFields("CoB Date").CurrentPage = num
End Sub

2 个解决方案

#1

As commented the exact same code works on my end.

正如评论所说，完全相同的代码在我的最终。

Sub My_macro()
    Dim num As String
    num = InputBox(Prompt:="Date", Title:="ENTER DATE")
    Sheets("Sheet1").PivotTables("PivotTable1") _
        .PivotFields("CoB Date").CurrentPage = num
End Sub

Suppose you have a data like this:

假设您有这样的数据：

When you run the macro, it will prompt for a date:

运行宏时，它将提示输入日期：

And then after pressing ok, the result would be:

然后按下确定后，结果将是：

Take note that we assumed that entering of date is not an issue.
So we used a simple data which will eliminate that and so your code works.
The probable issue you're dealing with is if the dates have Time Stamp.
And based on your screen shot, that is the case.

请注意，我们假设输入日期不是问题。所以我们使用了一个简单的数据来消除它，所以你的代码可以工作。您正在处理的可能问题是日期是否有时间戳。根据你的屏幕截图，就是这样。

#2

I had the same problem with "Unable to get PivotFields property of the PivotTable class".

我遇到了“无法获取数据透视表类的PivotFields属性”的问题。

I figured out that while my pivot table was the only one on that sheet (or in the workbook for that matter) it was not "PivotTable1". It was "PivotTable4" most likely because I had created and deleted 3 others beforehand.

我发现虽然我的数据透视表是该工作表上的唯一一个（或在工作簿中），但它不是“数据透视表1”。最有可能是“PivotTable4”，因为我之前创建并删除了其他3个。

To find out the name of your pivot table, and change it if you want, just right click anywhere in your pivot table and then select "Pivot Table Options". You will see the "PivotTable Name" right at the top and can rename it from there.

要查找数据透视表的名称，并根据需要进行更改，只需右键单击数据透视表中的任意位置，然后选择“数据透视表选项”。您将在顶部看到“数据透视表名称”，并可以从那里重命名。

Additionally, I was having issues with this same error when trying to change one of the filters. When I referenced the field using:

此外，在尝试更改其中一个过滤器时，我遇到了同样的错误问题。当我使用以下参考字段时：

Sheets("mySheetName").PivotTables("PivotTable4").PivotFields("myFieldName")

it would throw the same error as above. It turned out I had 3 spaces at the end of my field name. The way I found this out may was to loop through all my filter fields displaying a MsgBox for each until I found it. The following code is how I did that and hopefully may help someone with a similar issue:

它会抛出与上面相同的错误。事实证明我的字段名称末尾有3个空格。我找到这个的方法可能是遍历我的所有过滤器字段，每个过滤器字段显示一个MsgBox，直到我找到它。以下代码是我如何做到这一点，并希望可以帮助有类似问题的人：

Dim pt As PivotTable
Dim pf As PivotField

Set pt = Sheets("mySheetName").PivotTables("PivotTable4")
For Each pf In pt.PivotFields
    MsgBox ("FieldName: [" & pf.Name & "] with a length of: " & Len(pf.Name) & " and a trimmed length of: " & Len(Trim(pf.Name)))
Next pf

Hope this helps someone!

希望这有助于某人！