Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    54
    Thanks
    6
    Thanked 1 Time in 1 Post

    VBA Excel 2010 - Pivot Tables

    In Excel 2010, my Pivot Tables are not updating correctly when I open them. To solve the problem I have been manually adjusting the sort order by selecting to sort ascending and then descending. I thought a simple VBA macro would assist me in this task but I am having trouble circling through the many pivot tables I have on multiple worksheets in my workbook. The following code does not work. I do not understand Excel's object model well enough to clean the code. Any suggestions?

    Thank you in advance.
    Larry


    Sub UpdatePivot()
    '
    ' UpdatePivot Macro
    '

    '
    Dim pvt As PivotTable
    Dim sht As Worksheet

    For Each sht In Sheets
    For Each pvt In pivotables
    sht.PivotTables(pvt).PivotFields("Last Trade").AutoSort xlAscending, "Last Trade"
    sht.PivotTables(pvt).PivotFields("Last Trade").AutoSort xlDescending, "Last Trade"
    Next pvt
    Next sht

    End Sub

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by lmo View Post
    In Excel 2010, my Pivot Tables are not updating correctly when I open them. To solve the problem I have been manually adjusting the sort order by selecting to sort ascending and then descending. I thought a simple VBA macro would assist me in this task but I am having trouble circling through the many pivot tables I have on multiple worksheets in my workbook. The following code does not work. I do not understand Excel's object model well enough to clean the code. Any suggestions?

    Thank you in advance.
    Larry


    Sub UpdatePivot()
    '
    ' UpdatePivot Macro
    '

    '
    Dim pvt As PivotTable
    Dim sht As Worksheet

    For Each sht In Sheets
    For Each pvt In pivotables
    sht.PivotTables(pvt).PivotFields("Last Trade").AutoSort xlAscending, "Last Trade"
    sht.PivotTables(pvt).PivotFields("Last Trade").AutoSort xlDescending, "Last Trade"
    Next pvt
    Next sht

    End Sub
    Since no-one else has replied I'll throw in a few suggestions:
    what do you mean when you say the tables aren't updating correctly - do you mean showing fresh data, or not sorting automatically?
    I tend to use code like
    Code:
    for each ws in activeworkbook.worksheets
    for each pt in ws.PivotTables
    ' do something with the pivottable pt
    next pt
    next ws
    - see if the above code works for you.

    Also, if PT is a pivot table, try PT.PivotCache.Refresh to refresh the pivot table.

    Let me know how you get on!
    Last edited by RetiredGeek; 2013-12-01 at 15:26. Reason: Added code tags.

Posting Permissions

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