Run macro when tab is selected

[Barry Katcher]

Hello, peeps.

How do I get the following macro (that refreshes a pivot table) to run when I select the worksheet tab that the Pivot Table is on?

Sub RefreshPT()
'
' RefreshPT Macro
' Refresh Pivot Table
'

'
Range("E20").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Tanks,

Barry

 

[sous2817]

Wouldn't putting the code inthe Worksheet_Activate event (or calling the macro from that event) take care of it?

 

[Barry Katcher]

Yes, but that's exactly what I'm not sure how to do. Can you explain?

Barry

 

[sous2817]

i can try...

Right click on the sheet you want the activation code to be in and select "View Code". In the code window paste in:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

and see if that works.

 

[Barry Katcher]

Thanks, that works perfectly. Guess the reason I didn't think of it is that I'm an old geezer and can't remember squat anymore.

Barry

 

[sous2817]

Glad it all worked out. Don't be silly, I'm barely in to my 30s and I can hardly remember why I got out of bed most days!

Best of luck with the rest of your project!