I am having trouble with a macro in Excel 2010 that used to work just fine in Excel 2003.
The problem is with changing a PivotTable Page field in the macro. An example of the code is below.
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = year1
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = year1 - 1
I am changing the value in the Page field, which changes the values in the pivot table. This works just fine as is.
When I add in a line to refresh the pivot cache between changing the year, then I get an error.
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = year1
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = year1 - 1
After I refresh the cache, then when I try to change the Page field again, I get an error:
'Run-time error '5': Invalid procedure call or argument'
Anybody have any ideas?
I dont think this line is required. Once you select the value for the Page and then change it again, the pivot gets refreshed automatically. Try without the above line and see if you get any errors.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks