控制筛选Excel数据透视表从单元格值与VBA

Question

我正在尝试根据两个单元格值刷新我的powerpivot数据透视表。最终用户将使用单元格C17和C18中的下拉列表来派生单元格G17和G18中的值(通过一些excel计算)。数据透视表将基于单元格G17和G18进行刷新。

我已经编写了如下代码，但它似乎不工作，因为数据透视表不刷新后，我从下拉列表中选择一个值在C17和C18。

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("G17:G19")) Is Nothing Then Exit Sub
Dim pt As PivotTable
Dim FieldHr As PivotField
Dim FieldEndingMin As PivotField
Dim NewHr As String
Dim NewMin As String
Set pt = Worksheets("Calculator").PivotTables("Table1")
Set FieldHr = pt.PivotFields("Hr")
NewHr = Worksheets("Calculator").Range("G17").Value
Set FieldEndingMin = pt.PivotFields("Ending Min")
NewMin = Worksheets("Calculator").Range("G18").Value
With pt
FieldHr.ClearAllFilters
FieldHr.CurrentPage = NewHr
FieldEndingMin.ClearAllFilters
FieldEndingMin.CurrentPage = NewMin
pt.RefreshTable
End With
End Sub

Answer 1

我将代码从Worksheet_SelectionChange移动到Worksheet_Change事件。

我添加了范围的Union，单元格C17:C18与单元格G17:G19(需要G19吗?在你的文章中，你提到了G17:G18)。

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("C17:C18"), Range("G17:G19"))) Is Nothing Then Exit Sub
Dim pt                 As PivotTable
Dim FieldHr            As PivotField
Dim FieldEndingMin     As PivotField
Dim NewHr              As String
Dim NewMin             As String
Set pt = Worksheets("Calculator").PivotTables("Table1")
Set FieldHr = pt.PivotFields("Hr")
NewHr = Worksheets("Calculator").Range("G17").Value
Set FieldEndingMin = pt.PivotFields("Ending Min")
NewMin = Worksheets("Calculator").Range("G18").Value
With FieldHr
    .ClearAllFilters
    .CurrentPage = NewHr
End With
With FieldEndingMin
    .ClearAllFilters
    .CurrentPage = NewMin
End With
pt.RefreshTable
End Sub