excel - VBA使用选定的列过滤器创建一个新的数据透视表

Question

我想做的是：

如果单元格“J5”的值发生变化，那么我的数据透视表会创建一个新的：

动作过滤器“Actionstr”

SERVICE 列，只有一列“Servicestr”

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xPTable As PivotTable
Dim ActionStr As String
Dim ServiceStr As String

On Error Resume Next

If Intersect(Target, Range("J5")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False

Set xPTable = Worksheets("centrum1").PivotTables("test")

ActionStr = Range("G5").Text
ServiceStr = Range("G2").Text

With xPTable
    .ManualUpdate = False
    .ClearAllFilters
    .PivotFields("ACTION").PivotFilters.Add Type:=xlCaptionEquals, Value1:=ActionStr
    .PivotFields("SERVICE").PivotFilters.Add Type:=xlCaptionEquals, Value1:=ServiceStr
    .ManualUpdate = True
End With

Application.ScreenUpdating = True End sub

我这样做只是为了“过滤器”

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim Actionstr As String

On Error Resume Next
If Intersect(Target, Range("J5")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False

Set xPTable = Worksheets("centrum1").PivotTables("test")
Set xPFile = xPTable.PivotFields("ACTION")
Actionstr = Range("G5").Text
xPFile.ClearAllFilters
xPFile.CurrentPage = Actionstr
Application.ScreenUpdating = True End Sub

但它只会更改“过滤器”，它不会创建基于 Servicestr 的列。

感谢您的任何提示。

标签： excelvbapivot-table