Hi everyone,
I am working in a workbook with this code and it works well when updating one pivot table according to cell CV9. However, I now have 5 more pivot tables in the same worksheet which needs to be updated with the change in cell CV9 as well.
By illustration, the PivotFields that requires to be updated is called '4 Liq' for 'PivotTable5' and '4 AR' for 'PivotTable6' and so forth.
Is there a way to update the code below to include these pivot tables?
Also is there a way that the pivot table is updated automatically as per cell CG13 without having to hit enter at cell CG13. This is because I will be turning cell CG13 into a dropdown list instead of having user inputting a value cell CG13.
Many thanks.
I am working in a workbook with this code and it works well when updating one pivot table according to cell CV9. However, I now have 5 more pivot tables in the same worksheet which needs to be updated with the change in cell CV9 as well.
By illustration, the PivotFields that requires to be updated is called '4 Liq' for 'PivotTable5' and '4 AR' for 'PivotTable6' and so forth.
Is there a way to update the code below to include these pivot tables?
Also is there a way that the pivot table is updated automatically as per cell CG13 without having to hit enter at cell CG13. This is because I will be turning cell CG13 into a dropdown list instead of having user inputting a value cell CG13.
Many thanks.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell CV9 is touched
If Intersect(Target, Range("CG13")) Is Nothing Then Exit Sub
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = ActiveSheet.PivotTables("PivotTable4")
Set Field = pt.PivotFields("4 OP")
NewCat = ActiveSheet.Range("CG13").Value
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Last edited:
