Automatically Refreshing a Pivot Table

[Mopacs]

I have a series of files, each of which contains a small pivot table summarizing a data set on another sheet within each spreadsheet. I am having to manually "refresh" the pivot table so that it updates itself based on updated data (in the other sheet). Upon updating/changing the source data, is there a way to have the pivot table automatically refresh the moment you open the spreadsheet (without having to 'right click' on the pivot table and hit refresh, etc)?

Any help would be greatly appreciated here.

Thanks,

Rob

[Iain Lewis]

Have you tried using the Event Handler on the sheet containing the data so that when it changes or re-calculates then it refreshes that data in the Pivot Table ?

Give me a shout if you'd like a hand with the code to do this.

Iain

[Mopacs]

Hey there,

No I sure havent.. and if you wouldnt mind walking me through the event handler, I'd certainly appreciate it.

Thanks,

Rob

On 2002-04-23 08:33, Iain Lewis wrote:
Have you tried using the Event Handler on the sheet containing the data so that when it changes or re-calculates then it refreshes that data in the Pivot Table ?

Give me a shout if you'd like a hand with the code to do this.

Iain

[Audiojoe]

Put this baby in there:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

Audiojoe

[Mark W.]

On 2002-04-23 08:30, Mopacs wrote:
I have a series of files, each of which contains a small pivot table summarizing a data set on another sheet within each spreadsheet. I am having to manually "refresh" the pivot table so that it updates itself based on updated data (in the other sheet). Upon updating/changing the source data, is there a way to have the pivot table automatically refresh the moment you open the spreadsheet (without having to 'right click' on the pivot table and hit refresh, etc)?

Any help would be greatly appreciated here.

Thanks,

Rob

Why don't you just check the "Refresh on open" data option. Just right-click on the PivotTable and choose Options... from the popup menu.

[Mopacs]

Unfortunately I'm getting an error when executing this code.. I'm guessing it has to do with the Pivot table "name" This is a simple question, but how do you confirm/determine the actual name of the pivot table? I dont think I went in and deliberately named the pivot table..so I'm going under the assumption that the pivot table is given a default name (such as "PivotTable1", etc, which is what I left in the code..and hence bombed : (

Also, I notice that under Pivot "Table Options" (via pivot table toolbar menu) there is an option to "automatically refresh pivot table upon opening a file" Thats exactly what I want it to do! only question is, will this setting apply to ALL workstations that open this file, or will this setting only apply on THIS (my) computer?

Thanks for all your Help,

Rob

On 2002-04-23 08:44, Audiojoe wrote:
Put this baby in there:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

Audiojoe

[Mark W.]

On 2002-04-23 08:54, Mopacs wrote:
Also, I notice that under Pivot "Table Options" (via pivot table toolbar menu) there is an option to "automatically refresh pivot table upon opening a file" Thats exactly what I want it to do! only question is, will this setting apply to ALL workstations that open this file, or will this setting only apply on THIS (my) computer?

Thanks for all your Help,

Rob

Yes, it will work on any computer... and, it doesn't require any code!!! That's why I recommended it above.

[ This Message was edited by: Mark W. on 2002-04-23 09:03 ]

[Iain Lewis]

Can you send me a copy of the file ? It's a bit long winded to write out here, but I will send you instructions of how I did it.

[Mopacs]

Hey lain,

No thats ok, dont go through any trouble... it seems I have figured it out.. it was kind of under my nose the whole time!

Mark: Thanks... That was my main concert about the "Table options"..whether it is limited to the workstation/application it is set on. I'm guessing this is analagous to setting the "Freeze Window" option in a worksheet...which is viewable on any computer that file is sent to?

Thanks again (all of you),

Rob

On 2002-04-23 08:59, Iain Lewis wrote:
Can you send me a copy of the file ? It's a bit long winded to write out here, but I will send you instructions of how I did it.

[ This Message was edited by: Mopacs on 2002-04-23 09:25 ]