vba 运行时错误 1004：无法获取 Worksheet 类的数据透视表属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32892267/
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
Runtime Error 1004: Unable to get the PivotTables property of the Worksheet class
提问by ayyzad
I recorded this macro to update the date range of 16 charts. But, as you can see I am getting a Run-time error.
我录制了这个宏来更新 16 个图表的日期范围。但是，正如您所看到的，我收到了一个运行时错误。
I have looked at other threads on stackoverflow that relate to this but none come close. The darn help button on excel doesn't help either. Can you please advise? Here is the code:
我已经查看了与此相关的 stackoverflow 上的其他线程，但没有一个接近。excel 上的该死的帮助按钮也无济于事。你能给些建议么？这是代码：
ActiveSheet.ChartObjects("Chart 18").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Chart 18").Activate
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
.PivotItems("Sep-15").Visible = False
.PivotItems("Aug-14").Visible = True
End With
采纳答案by Josh Fierro
Here's some simple code that should get you close. You will need to change "MySheet" in this code to the name of the sheet containing the pivot table in your workbook, and your Date field must truly be text formatted in the "Mmm-YY" format.
这里有一些简单的代码，应该能让你接近。您需要将此代码中的“MySheet”更改为包含工作簿中数据透视表的工作表的名称，并且您的“日期”字段必须真正是“Mmm-YY”格式的文本格式。
Sub ShowThirteenDatesStartingLastMonth()
Sheets("MySheet").PivotTables("PivotTable2").PivotCache.Refresh
Dim Dt As String
With Sheets("MySheet").PivotTables("PivotTable2").PivotFields("Date")
For h = 1 To .PivotItems.Count - 1
On Error Resume Next
.PivotItems(h).Visible = False
Next h
For i = 1 To 13
On Error Resume Next
Dt = Format(DateSerial(Year(Date), Month(Date) - i, 1), "Mmm-YY")
.PivotItems(Dt).Visible = True
Next i
End With
End Sub
回答by Josh Fierro
It's hard to know without an example of your workbook, but I would try naming the sheet rather than using "activesheet". It's also typically a bad idea to activate or select anything, and instead you should allow the code to do as much work as it can without selecting anything. Something like this might work better:
如果没有您的工作簿示例，很难知道，但我会尝试命名工作表而不是使用“activesheet”。激活或选择任何东西通常也是一个坏主意，相反，您应该允许代码在不选择任何东西的情况下做尽可能多的工作。像这样的事情可能会更好：
With Sheets("MySheet").PivotTables("PivotTable2").PivotFields("Date")
.PivotItems("Sep-15").Visible = False
.PivotItems("Aug-14").Visible = True
End With
回答by MJ95
As Josh said, you should reference the exact sheet name and maybe even the exact Pivot table name.
正如 Josh 所说，您应该引用确切的工作表名称，甚至可能是确切的数据透视表名称。
Also, you should avoid using .Selectand instead declare a variable for each pivot chart, for example.
此外，例如，您应该避免使用.Select并为每个数据透视图声明一个变量。
