Worksheet Activate Event

[Guest]

I have the following code, which is being run when a worksheet called "RV
Summary" is being selected.

Private Sub Worksheet_Activate()

Sheets("Crosstab").Activate
ActiveSheet.PivotTables("PivotTable_Statistics").PivotCache.Refresh
Sheets("RV Summary").Select

End Sub

Whenever "RV Summary" is selected, I want a pivot table located on another
sheet (Crosstab) to get refreshed, and then I want it to maintain "RV
Summary" as the active sheet (essentially the user will not even know that
this pivot is being updated when they select (RV Summary). The problem is
that this gets into a never ending loop. Is their a way to alter this or
make it so that it only performs this action once and then just goes back to
RV Summary. Thanks.

 

[Myrna Larson]

You don't need to select and/or activate things to work with them and doing so
may cause real problems if you don't code it correctly. YOu've run into that
-- inadvertent recursion.

At the very least, if you *had* to activate a different sheet, you would
disable events (Application.EnableEvents = False), then activate the other
sheet, then re-enable just before the routine ends.

But there is no need to change sheets here. Try it this way:

Private Sub Worksheet_Activate()
Sheets("Crosstab").PivotTables("PivotTable_Statistics").PivotCache.Refresh
End Sub