Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
- 'This line stops the worksheet updating on every change, it only updates when cell
- 'A1 to A9 is touched
- If Intersect(Target, Worksheets(1).Range("A1:A9")) 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 suit your data
- Set pt = Worksheets(1).PivotTables("PivotTable1")
- Set Field = pt.PivotFields("Sales Region")
- NewCat = Worksheets(1).Range("A1:A9").Value
- 'This updates and refreshes the PIVOT table
- With pt
- Field.ClearAllFilters
- Field.CurrentPage = NewCat
- pt.RefreshTable
- End With
- End Sub
Add Comment
Please, Sign In to add comment