Auto Refresh Pivot Table with Auto Calc (2003)

Topic

New Reply

Is there a way to automatically refresh a pivot table’s results at the same time a change is made in a calculated field?

Example: I have a drop down box based on a very long list. Upon choosing one item, several subs fields are returned in another column. I have the pivot table based on what is returned from the first selection. I don’t want the user to have to select refresh to get updated data. I want the pivot table to refresh automatically. Does this make sense or is it just me????

Reply | Quote

Replies

You could update the pivot table in the On Calculate event of the worksheet:
– Right-click the worksheet tab.
– Select View Code from the popup menu.
– Enter or paste the following code into the worksheet module:

Private Sub Worksheet_Calculate()
Me.PivotTables(1).RefreshTable
End Sub

– Note: this code will run each time something is calculated on your worksheet. It may have a negative impact on performance.

Reply | Quote

It didn’t work. I still have to refresh.

Reply | Quote

Could you attach (a stripped down copy of) the workbook? Replace sensitive data with dummy data, if necessary.

Reply | Quote

Maybe it’s just me being dumb and not knowing that I can do this in a much easier way. Also, I know that there are better ways to create forms, but based on external limitations, I have to keep with Excel right now.

I am just trying to return a list of the results to the second drop down box without the user having to scroll down. I want the selections right at the top. Thanks.

Reply | Quote

The code should be behind the ‘data’ sheet, because that’s the sheet that is being updated by the dropdown lists on the ‘help_form’ sheet. And the code should update the pivot table on yet another sheet, Sheet4. See attached version.

Reply | Quote

This worked PERFECTLY. You guys have been such a great help over the years. Thank you very much.

Reply | Quote

Going back to this scenario, I am using it again for another file. This time, I have additional pivot tables on the sheet. I went into “view code” for the sheet and updated the details to reflect 2 pivot tables, but it’s not working. what am i doing wrong?

Private Sub Worksheet_Calculate()
Me.PivotTables(2).RefreshTable
End Sub

Much gratitude as always!

Reply | Quote

You can use the following code to update all pivot tables on the worksheet, regardless of their number:

Code:

  Dim pt As PivotTable
  For Each pt In Me.PivotTables
    pt.RefreshTable
  Next pt

Reply | Quote

hhhmmmm, didn’t work. putting this in and overwriting the original caused all of it to stop working. no auto-calc took place at all. thoughts?

Reply | Quote

Apparently it causes a cascade of events. Try this version:

Code:

Private Sub Worksheet_Calculate()
  Dim pt As PivotTable
  Application.EnableEvents = False
  For Each pt In Me.PivotTables
    pt.PivotCache.Refresh
  Next pt
  Application.EnableEvents = True
End Sub

Reply | Quote

You’re always my rock star, Hans! Thanks again!!!!

Reply | Quote

Hi,

The text of the original message imply that there were attachments. Did they get lost at some point?

–Scott.

Reply | Quote

The text of the original message imply that there were attachments. Did they get lost at some point?

Yes, our server hard disk crashed in August, 2007, and although most could be restored, some 15% of the attachments was irretrieveably lost.

I don’t have a copy of the file that I uploaded anymore.

Reply | Quote

Ok. I was wanting to look to learn more about PivotTables, and scripting them; I discovered the annoying way that PivotTables created with a recorded macro don’t always look the same as what I recorded.

–Scott.

Reply | Quote