获取“无法设置PivotField类的CurrentPage属性”错误 [英] Getting "Unable to set CurrentPage property of PivotField class" error

问题描述

我试图使用VBA根据用户从单元格条目的选择自动更新数据透视表过滤器。我相信一切都正常运行到第11行，它说Field.CurrentPage = NewCat。这就是问题所在。代码按照上一行的指示清除了我的过滤器，但是当它到达指令它选择新数据的代码时，我得到了读取运行时错误1004.无法设置PivotField类的CurrentPage属性的错误。 p>

以下是我到目前为止，我只是看看如何修改第11行，以便选择在数据透视表过滤器中使用的新输入。我感谢任何帮助我可以得到这个。我对vba&已经挣扎了太久了！

  Private Sub Worksheet_SelectionChange（ByVal Target As Range）
 
如果相交（目标，范围（C3：C4））没有，然后退出Sub 
 
 Dim pt As PivotTable 
 Dim Field As PivotField 
 Dim NewCat As String 
 
设置pt =工作表（Pivot）数据透视表（PivotTable1）
设置字段= pt.PivotFields（[Range]。[Site]。[Site]）
 NewCat = Worksheets（Interface）。Range（C3）。值
 
带有pt 
 Field.ClearAllFilters 
 Field.CurrentPage = NewCat 
 pt.RefreshTable 
结束
 
结束Sub 
  

解决方案<

如果您更改：

 使用pt 
 Field.ClearAllFilters 
 Field.CurrentPage = NewCat 
 pt.RefreshTable 
结束
  

to：

 使用字段
。 ClearAllFilters 
 .CurrentPage = Ne wCat 
结束
 
 pt.RefreshTable 
  

它将工作。

注意，我直接与 Field 在 With Block 。

为了进一步说明，如果您将其更改为以下内容，它也可以正常工作：

pre $ $ b $ C $ $ $ $ $ $ $ $ $ $ .RefreshTable


所以，你基本上需要直接使用对象，你需要限定对象，属性，方法等等。。

I'm trying to use VBA to automatically update the pivot table filter based off the user's selection from a cell entry. I believe everything is working correctly up to line 11 which says "Field.CurrentPage = NewCat". This is where the issue lies. The code clears my filter as instructed in the previous line but when it gets to the code instructing it to select the new data I get the error that reads "Run-time error 1004. Unable to set CurrentPage property of PivotField class".

Below is the what I have so far and I'm just looking how to revise row 11 so it selects the new input to use in the pivot table filter. I appreciate any help I can get on this. I'm very new to vba & have struggled with this for far too long!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("C3:C4")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("Pivot").PivotTables("PivotTable1")
Set Field = pt.PivotFields("[Range].[Site].[Site]")
NewCat = Worksheets("Interface").Range("C3").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

解决方案

The answer is just a matter of syntax.

If you change:

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

to:

With Field
    .ClearAllFilters
    .CurrentPage = NewCat
End With

pt.RefreshTable

It will work.

Notice, that I work directly with the Field in the With Block.

To illustrate further, if you change it to the following, it will work as well:

With pt.PivotFields(Field.Name)
    .ClearAllFilters
    .CurrentPage = NewCat
End With

pt.RefreshTable

So, you basically need to work directly with objects and you need to qualify the objects, properties, methods / etc. with a .

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