如何使用 VBA 从单元格值控制 Excel PIVOT 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25999815/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to control Excel PIVOT tables from cell values with VBA
提问by Larry
I'm trying to refresh my pivot table base on two cell values. End user will type in value in cell B4 (Region) and value in cell B5 (Department). Pivot table would refresh base on these values.
我正在尝试根据两个单元格值刷新我的数据透视表。最终用户将在单元格 B4（区域）中输入值，在单元格 B5（部门）中输入值。数据透视表将根据这些值刷新。
I found code below, but only allows for referencing 1 cell value. Not sure how to modify it for 2 cell values.
我在下面找到了代码，但只允许引用 1 个单元格值。不确定如何为 2 个单元格值修改它。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'B4 or B5 is touched
If Intersect(Target, Range("B4:B5")) Is Nothing Then Exit Sub
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Here you amend to filter your data
Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Region")
NewCat = Worksheets("Sheet1").Range("B4").Value
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
回答by Larry
I was able to use the code below to update my Pivot table filters. Hope this is helpful to others.
我能够使用下面的代码来更新我的数据透视表过滤器。希望这对其他人有帮助。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This line stops the worksheet updating on every change, 'it only updates when cell'B4
'or B5 is touched
If Intersect(Target, Range("B4:B5")) Is Nothing Then Exit Sub
'Set the Variables to be used
Dim pt As PivotTable
Dim FieldRegion As PivotField
Dim FieldDept As PivotField
Dim NewRegion As String
Dim NewDept As String
'Amend here to filter your data
Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
Set FieldRegion = pt.PivotFields("Region")
Set FieldDept = pt.PivotFields("Department")
NewRegion = Worksheets("Sheet1").Range("B4").Value
NewDept = Worksheets("Sheet1").Range("B5").value
'This updates and refreshes the PIVOT table
With pt
FieldRegion.ClearAllFilters
FieldRegion.CurrentPage = NewRegion
FieldDept.ClearAllFilters
FieldDept.CurrentPage = NewDept
pt.RefreshTable
End With
End Sub
