Hi All,
I wondering if someone would be able to help.
I have a workbook that has a sheet labelled as "Pivots" with the data kept in another sheet called "Calls"...I am trying to find a way that would allow me to update the data in "calls" and for the pivots (named as PivotTable1 to 9) to refresh automatically with only the latest values showing (I run the report in the morning for the previous day).
The layout of the PTs are:
ROWS:
Date
Description
Columns:
Values (Sum of total calls & Sum of Total Talk Time)
I have tried the below on the "Calls" sheet in VBA under Worksheet and Change but this requires me to have all of the dates showing under the filter (when I need the latest date only showing):
Many thanks for any help / advice.
I wondering if someone would be able to help.
I have a workbook that has a sheet labelled as "Pivots" with the data kept in another sheet called "Calls"...I am trying to find a way that would allow me to update the data in "calls" and for the pivots (named as PivotTable1 to 9) to refresh automatically with only the latest values showing (I run the report in the morning for the previous day).
The layout of the PTs are:
ROWS:
Date
Description
Columns:
Values (Sum of total calls & Sum of Total Talk Time)
I have tried the below on the "Calls" sheet in VBA under Worksheet and Change but this requires me to have all of the dates showing under the filter (when I need the latest date only showing):
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sub Refresh_All_Pivot_Table_Caches()
'Refresh all pivot caches in the workbook.
'Pivot tables are automatically refreshed when cache is refreshed.
Dim pc As PivotCache
'Refresh all pivot tables
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
Many thanks for any help / advice.