Thread: VBA Excel 2010 - Pivot Tables
2013-11-29, 20:03 #1
- Join Date
- Dec 2002
- San Francisco, California, USA
- 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.
' 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"
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!
+ 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!
2013-12-01, 13:39 #2
- Join Date
- Apr 2002
- Redditch, Worcestershire, England
- Thanked 21 Times in 20 Posts
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
for each ws in activeworkbook.worksheets for each pt in ws.PivotTables ' do something with the pivottable pt next pt next ws
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.