Ok I have 2 Worksheets, 1 "Report" contains my end report, 2 "Data" contains two pivot tables named "Pivot" and "Pivot1". Cell "B3" on "Report" is the cell I want to type a Shop number into and filter both pivot tables by that column. The column is named "Shop" in both tables. Below is the code I have to update one table and it works, for one, here are my questions
1)How do I update the second pivot table at the same time?
2) This runs a little slow and also the pivot tables update immediately, but I have to click another cell after it runs to make the "Report" update.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = Worksheets("Data").PivotTables("Pivot")
Set Field = pt.PivotFields("Shop")
NewCat = Worksheets("Report").Range("B3").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Bookmarks