Executing a command when clicking on a tab

[Guest]

I need to write a macro that will automatically start whenever a user clicks
on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do
execute the PivotTable "Refresh Data" command.
Can someone kindly tell me how to do this?
Thanks in advance for any help.

 

[Ron de Bruin]

Hi Bob

There is activate event for every worksheet

Copy this in a sheet module

Private Sub Worksheet_Activate()

End Sub

 

[Guest]

right click on the sheet tab, click on "View Code". Then paste this:

Private Sub Worksheet_Activate()
Dim pvt As PivotTable
For Each pvt In Me.PivotTables
pvt.PivotCache.Refresh
Next pvt
End Sub

 

[Guest]

Vergel,
Thanks for the help! Since there is only one PivotTable involved here, can
you tell me how to simplify your macro (since it appears that it covers
multiple PivotTables)?
Thanks again,
Bob

 

[Guest]

Bob,

You can replace the For Loop block with this one line:

Me.PivotTables(1).PivotCache.Refresh

 

[Guest]

Hi Ron,
Thanks for the info!
Bob

Hi Bob

There is activate event for every worksheet

Copy this in a sheet module

Private Sub Worksheet_Activate()

End Sub

 

[Guest]

Vergel,
Perfect! Thanks again for all your help.
Bob