Refresh data automatically in Excel Pivottable 1

[achau]

Is there a way to refresh the data in a pivot table (in a different worksheet from the raw data sheet) automatically in Excel? What I'd like to do is to allow the users to modify the numbers in the raw data sheet and then the numbers in the pivot table will be re-calculated automatically so that they don't have to worry about pressing on the refresh button.

Thanks.

Al.

 

[ETID]

Turn on the macro recorder,...and record a manual refresh of the pivot table, then stop the recorder.

press alt+F11 to view the code that the recorded has written

then copy just the text between the SUB (your macro name here) () and the end sub statements

then alt+F11 back to your worksheet,

then right click on the sheet name then select view code,

in the drop down marked (general) select worksheet then in the next drop down select activate.

and empty sub will appear looking like this...
---------------------------------
Private Sub Worksheet_Activate()

paste the recorded code here

End Sub
----------------------------------

AltF11 back to worksheet...



You may have to enable macros for this to work without prompts. (Tools>Macro>Security...then select low

Now when users select the pivot table sheet it will refresh...