Run Macro when changing tabs
 

I have a pivot table set up that runs off data from a different tabe in the
same workbook. The data will be refreshed monthly and some formatting needs
to be done to the data before refreshing the pivot table. I have set up a
macro that will do the formatting. Is there a way where the macro would run
as soon as you click on the tab for the pivot table?

Run Macro when changing tabs
 

In the PT sheet module

Private Sub Worksheet_Activate()

macroname(which will include references to the data sheet)

End Sub


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 12:26:09 -0800, Supe
wrote:

I have a pivot table set up that runs off data from a different tabe in the
same workbook. The data will be refreshed monthly and some formatting needs
to be done to the data before refreshing the pivot table. I have set up a
macro that will do the formatting. Is there a way where the macro would run
as soon as you click on the tab for the pivot table?

Run Macro when changing tabs
 

Hi,

You can also run the macro when refreshing the pivot table:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'your code here
End Sub

I only point this out as info, I think it sounds better to use the sheet
activate routine.

This goes into the Sheet object for the pivot table sheet in the VBA
editior, just like the one provided by Gord.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Supe" wrote:

I have a pivot table set up that runs off data from a different tabe in the
same workbook. The data will be refreshed monthly and some formatting needs
to be done to the data before refreshing the pivot table. I have set up a
macro that will do the formatting. Is there a way where the macro would run
as soon as you click on the tab for the pivot table?