Forum Discussion
VBA Code for Pivot Table refresh
Hello everyone!
I have this code, that refreshes all the pivot tables in the workbook.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim p As PivotCache
For Each p In ThisWorkbook.PivotCaches
p.Refresh
Next p
End Sub
My problem is, I am making a template where there are multiple source data worksheets and I made individual sheets for pivot tables for each of the source data. For example "Sales Hygiene Source" and "Sales Hygiene Pivot", "Execution Hygiene Source" and "Execution Hygiene Pivot", and so on. I need the pivot table of "Sales Hygiene Pivot" to refresh when I change the source data in "Sales Hygiene Source" and keep the other Pivot Tables as it is and the same for the rest of the source data worksheets and pivot table worksheets.
The aforementioned code doesn't seem to work for what I am intending to do. Can you please provide me an exact code to solve this with steps mentioned? I am very new to VBA and have close to zero clue about the syntax. Any help will be truly appreciated.
Right-click the sheet tab of the Sales Hygiene Pivot sheet.
Select View Code from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh End SubThis will update the pivot table each time you switch to the Sales Hygiene Pivot sheet to view the pivot table.
Do the same for the other sheets with pivot tables.
HansVogelaar Hey man! Thank you for the help. But the code says the reference is not valid. Do I need to change Me. PivotTables(1) to Me. PivotTables(2) and then (3) for the next pivot table sheets to make the code work?
Did you right-click the sheet tab of a sheet with a pivot table?
It won't work if you right-click the sheet tab of a sheet with the source data for a pivot table.
