Refresh Data on Sheet activation

[vavs]

I have a worksheet that contains form data and a pivot table. The pivot table is based on another worksheet in the same workbook. When the user goes to the worksheet with the pivot table, they need to refresh the data in order to see the corrections. What I would like to do is to refresh the data whenever the user selects this particular worksheet. I can do a ActiveWorkbook.RefreshAll on Worksheet Change. The only problem with this is that it refreshes everything in the workbook and that is substantial. Is there a way to refresh data automatically when I select a particular worksheet?

 

[marka87uk]

Something like this should work:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

 

[vavs]

Thanks for the reply. Here is what I ended up doing and it seems to be working.

On each of the affected worksheets, 3 in all, I put the following code:

/*
Private Sub Worksheet_Activate()

ActiveWorkbook.RefreshAll


End Sub
*/

When the user goes to the sheet, the sheet gets updated.

I also added some code that clears all of the fields on one of the sheets before exit. The client is extremely happy.