根据单元格中的文本字符串过滤数据透视表

I have the following code:

我有以下代码:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("K2:K3")) Is Nothing Then Exit Sub

    Dim pt As PivotTable
    Dim Field As Variant
    Dim NewCat As String
    Set pt = Worksheets("Fact Trans").PivotTables("PivotTable1")
    Set Field = pt.PivotFields("[Locations].[Loc Name].[Location Name]")
    NewCat = Worksheets("Fact Trans").Range("K2").Value
    With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
        pt.RefreshTable
    End With

End Sub

So as you can see I'm trying to change my pivot table based on the value in K2, but I keep getting the error "Run-time error '1004': Unable to set CurrentPage property of the PivotField class" and debug highlight's line "Field.CurrentPage = NewCat" .

因此,您可以看到我正在尝试根据K2中的值更改我的数据透视表,但我不断收到错误“运行时错误'1004':无法设置PivotField类的CurrentPage属性”并调试高亮显示的行“Field.CurrentPage = NewCat”。

Any insight on why and how I can get this code to work will be great.

任何关于为什么以及如何让这些代码工作的见解都会很棒。

1 个解决方案

#1

Here's the updated code

这是更新的代码

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Intersect(Target, Range("K2:K3")) Is Nothing Then Exit Sub

Dim pt As PivotTable

Dim Field As Variant

Dim NewCat As String


Set pt = Worksheets("Fact Trans").PivotTables("PivotTable1")

Set Field = pt.PivotFields("[Locations].[Loc Name].[Location Name]")

NewCat = Worksheets("Fact Trans").Range("K2").Value


With ActiveSheet.PivotTables("PivotTable1").PivotFields("[Locations].[Loc Name].[Location Name]")
    .ClearAllFilters

.PivotFilters.Add Type:=xlCaptionEquals, Value1:=ActiveSheet.Range("K3").Value

End With

End Sub