Auto refresh pivot table on internal data change

Question

I've a worksheet that has some data (sheet1) and another worksheet that has a pivot table(sheet2).

How do I make sure that when I update the data in sheet1 the sheet2 pivot table is updated?
a. How do I insure of an automatic update, or failing which
b. How do I manually refresh the pivot table to use the latest data

What I do know is that I can select 'Refresh data when opening the file' but I don't want to open and close the file.

TIA

Answer 1

a. How do I insure of an automatic update?

I don't think this is possible when your pivot source is in the same workbook. For external data sources you can set to refresh every x minutes.

b. How do I manually refresh the pivot table to use the latest data?

When the active cell is within a pivot table, you will have the Refresh option available under PivotTable Tools | Options on the Ribbon bar.

Answer 2

You can use VBA and the WorksheetChange or WorksheetCalculate events in the data worksheet to update the pivot table. I googled this but didn't find the detailed discussion I remembered, so here's a brief example. If you are only changing the data manually on the data sheet, then you'd just need the WorksheetChange event, but if the data is fed from other sheet(s) or workbook(s) you'd need the Calculate event:

Private Sub Worksheet_Calculate()
Call RefreshPT
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Call RefreshPT
End Sub

Sub RefreshPT()
Sheet1.PivotTables(1).PivotCache.Refresh
End Sub