Executing a command when clicking on a tab
 

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.

Executing a command when clicking on a tab
 

Hi Bob

There is activate event for every worksheet

Copy this in a sheet module

Private Sub Worksheet_Activate()

End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob" wrote in message ...
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.

Executing a command when clicking on a tab
 

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



--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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.

Executing a command when clicking on a tab
 

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


"Vergel Adriano" wrote:

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



--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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.

Executing a command when clicking on a tab
 

Bob,

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

Me.PivotTables(1).PivotCache.Refresh


--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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


"Vergel Adriano" wrote:

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



--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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.

Executing a command when clicking on a tab
 

Hi Ron,
Thanks for the info!
Bob


"Ron de Bruin" wrote:

Hi Bob

There is activate event for every worksheet

Copy this in a sheet module

Private Sub Worksheet_Activate()

End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob" wrote in message ...
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.

Executing a command when clicking on a tab
 

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


"Vergel Adriano" wrote:

Bob,

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

Me.PivotTables(1).PivotCache.Refresh


--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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


"Vergel Adriano" wrote:

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



--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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.