Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.