Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question
up vote 1 down vote accepted
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.

share|improve this answer

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()
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .