使用 VBA 更改数据透视表过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24240126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change the pivot Table filter using VBA
提问by pikachuchameleon
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 日期”字段的数据透视表，如图所示。
我正在尝试创建一个宏，它会根据用户输入自动更改日期。
我编写了以下宏代码。但它显示错误：
Unable to get PivotFields property of the PivotTable class
无法获取 PivotTable 类的 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
回答by L42
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.
请注意，我们假设输入日期不是问题。
所以我们使用了一个简单的数据来消除它，所以你的代码可以工作。
您正在处理的可能问题是日期是否有Time Stamp。
根据您的屏幕截图，情况就是如此。
回答by Brian H
I had the same problem with "Unable to get PivotFields property of the PivotTable class".
我在“无法获取 PivotTable 类的 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!
希望这可以帮助某人！
