Run Macro when changing tabs

S

Supe

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?
 
G

Gord Dibben

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
 
S

Shane Devenshire

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Macro Automatically When Excel 2003 Opens 2
Wait statement in macro 1
How do I replace/select new fields in pivot table using a macro? 1
Pivot ungrouping on refresh? 3
Run Macro when click on different tab in Excel 3
Timestamp for when Pivot table was last refreshed 1
copy a pivot table to another sheet in a macro 1
Macro on a Pivot Table 1

Top