How to control Excel PIVOT tables from cell values with VBA

Question

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.

I found code below, but only allows for referencing 1 cell value. Not sure how to modify it for 2 cell values.

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

Larry · Accepted Answer · 2014-09-26 15:08:17Z

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

Collectives™ on Stack Overflow

How to control Excel PIVOT tables from cell values with VBA

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related