[Solved] Pivot Tables : Automatic refresh of the pivot ta

steveski · Feb 17th 2004

I have a data entry sheet and a pivot table based on the data entry sheet. Is there a way to make the pivot table data refresh automatically based on some trigger (e.g., leaving the data entry sheet)?

Pesky Weasel · Feb 17th 2004

steveski,

First define your pivot data with a dynamic range name:

Click anywhere in your data and then Insert/Name/Define. Enter a name for your data - eg. myPivot - and in the refers to box enter

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
(where the data starts in A1)

Now go to your pivot table wizard and under step 2 of 3 ;Where is the data you want to use, enter =myPivot

On your Pivot Table sheet and right click the sheet tab, enter the following code (change name of pivot table where appropriate):

Code

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

Now every time you select the sheet with the pivot table the data will refresh to all entries on the data sheet.

Regards
Weasel

steveski · Feb 17th 2004

I have a data entry sheet, two pivot tables based on the data entry sheet, and two charts (not pivot charts) based on the pivot tables. One chart is located in a sheet, the other is a stand-alone chart. When I turn the file over to the user, the pivot tables will be hidden. I want both charts to automatically refresh when I click their tabs. For the chart located in the sheet, I have used the following code, which works great:

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

However, this code does not work if I use it with the stand-alone chart. Do I need to change something to make it work in the chart?

Thanks.

Pesky Weasel · Feb 17th 2004

steveski,

Chart sheets differ slightly from worksheets. Try:

Code

Private Sub Chart_Activate()
ActiveWorkbook.RefreshAll
End Sub

Regards
Weasel

steveski · Feb 17th 2004

Thanks, I tried it, but it doesn't seem to work for me. Does it work for you?

Pesky Weasel · Feb 17th 2004

steveski,

Try

Sheets("Sheet4").PivotTables("PivotTable1").PivotCache.Refresh

This can be placed in either the Chart_Activate module or the data sheet Worksheet_Change module. (where sheet4 is the sheet that contains your PT)

Regards
Weasel

steveski · Feb 17th 2004

Thanks Weasel, what should be put in place of "PivotTable1"?

Pesky Weasel · Feb 17th 2004

Replace this with the name of your Pivot Table.

To find out the name, right click somewhere in your PT and choose Options. The first bit of info is a textbox showing the name of your PT. You can use this to change the name if required.

Regards
Weasel

steveski · Feb 17th 2004

Sorry for that last question, it was lame.

Thanks! It works perfectly!

Pesky Weasel · Feb 17th 2004

No Problem & You're Welcome.

Iridium · Feb 17th 2004

Just as an aside there's a macro on Chip Pearson's site at http://www.cpearson.com/excel/pivots.htm which you could modify to use an event to get your answer

HTH

steveski · Feb 17th 2004

OK, what if I want a PivotChart to automatically refresh?

Should this code still work?

Private Sub Chart_Activate()
Sheets("PV1Started").PivotTables("PivotTable2").PivotCache.Refresh
End Sub

[Solved] Pivot Tables : Automatic refresh of the pivot ta

Participate now!

Share