Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Auto Refresh Pivot Table with Auto Calc (2003)

    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????
    thanks
    christine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Refresh Pivot Table with Auto Calc (2003)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto Refresh Pivot Table with Auto Calc (2003)

    It didn't work. I still have to refresh.
    thanks
    christine

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Refresh Pivot Table with Auto Calc (2003)

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

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto Refresh Pivot Table with Auto Calc (2003)

    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.
    thanks
    christine

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Refresh Pivot Table with Auto Calc (2003)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto Refresh Pivot Table with Auto Calc (2003)

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

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!
    thanks
    christine

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  10. #10
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?
    thanks
    christine

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  12. #12
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    You're always my rock star, Hans! Thanks again!!!!
    thanks
    christine

  13. #13
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

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

    --Scott.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Scott McNay View Post
    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.

  15. #15
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •