如何使用VBA从单元格值控制Excel PIVOT表格
问题描述:
我想刷新基于两个单元格值的数据透视表。最终用户将在单元格B4(区域)中键入值,并在单元格B5(部门)中键入值。数据透视表将刷新基于这些值。如何使用VBA从单元格值控制Excel PIVOT表格
我发现下面的代码,但只允许引用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
答
我能够使用下面的代码来更新我的数据透视表过滤器。希望这对其他人有帮助。
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