无法设置pivotItem类的Visibility属性

Question

我有一个Excel文件，当我按名称进行筛选时，创建了Pivot和Two过滤器(主要供应商名称和日期(

我想过滤每个主要供应商名称，一次只应选择过滤器中的一个值，另一个应取消选择我试过这个密码。

Dim ws As Worksheet
Set ws = Worksheets("3rd Pivot")
Dim pt As PivotTable
Set pt = ws.PivotTables("secondpivot_table")

Dim pF As PivotField
Set pF = pt.PivotFields("Principal Vendor Name")
pF.EnableMultiplePageItems = True
pF.CurrentPage = "(All)"

For i = 1 To pF.PivotItems.Count
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

pt.PivotCache.Refresh
k = i
For j = 1 To pF.PivotItems.Count
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh

If i = j Then
pF.PivotItems(k).Visible = True
Else
pF.PivotItems(j).Visible = False
End If
Next j

但这一行显示错误pF.PivotItems(j(.Visible=False"无法设置pivotItem类的Visibility属性"；

Answer 1

类似这样的东西：

Sub Tester()
Dim ws As Worksheet, pt As PivotTable, pF As PivotField
Dim i As Long, n As Long

Set ws = Worksheets("3rd Pivot")
Set pt = ws.PivotTables("secondpivot_table")
Set pF = pt.PivotFields("Principal Vendor Name")
pF.EnableMultiplePageItems = True
pF.CurrentPage = "(All)"

For i = 1 To pF.PivotItems.Count

pF.PivotItems(i).Visible = True 'set the visible one first
'then hide the rest
For n = 1 To pF.PivotItems.Count
If n <> i Then pF.PivotItems(n).Visible = False
Next n

Next i
End Sub