My workbook updates multiple pivot tables with the code below. This works fine when processing smaller amounts of data, but it severely lags when processing large amounts of data. It shouldn't be taking that long to make the updates. Is there a more efficient way for me to update all of my pivot table? They all use the same source data from the Data worksheet.
Please Login or Register to view this content.
If you use a Table or dynamic range for the source data, you won't need to change it - you'll just need to refresh the tables. As it is, your code actually refreshes everything twice which isn't helping the speed.
Remember what the dormouse said
Feed your head
Thanks for the reply. I feel like I'm in over my head on this. I created a dynamic data range (DataRange), but the code errors out on the pt.RefreshTable and the dynamic data range name (DataRange) breaks down and puts in#REF! instead of the desired refence cells.
DataRange name function Before code runs:
DataRange name function After code runs:Please Login or Register to view this content.
VBA Code Error here:Please Login or Register to view this content.
I also tried to use pt.ManualUpdate = True/False to try to speed up the code, but the tables don't refresh until I manually hit the refresh all button once the code finishes (it does run faster this way though).Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Platinum3x; 12-11-2014 at 11:47 AM.
Does your code delete rows on the data sheet?
Yes, it essentially pulls in data from another excel workbook and pastes it into the Data tab of my workbook where all these macros are. Then it uses that source data on the Data tab to update all the pivot tables on the various sheets. So, when that new source data gets copied over to the Data tab worksheet, I'm sure it's messing with the OFFSET formula of my dynamic range.
Right now I'm forced to set the source data or else I get an error. The pivot tables will change source data ranges on me:
If I don't use this:
I will get this error (if just using pivot table refresh):Please Login or Register to view this content.
Please Login or Register to view this content.
What's the code that pulls in the new data? There should be no reason for it to actually delete cells - if it just clears them, you shouldn't have a problem with the dynamic range, or a Table which would be simpler.
Here's the start of my code that brings in the new source data as the first step. I do see the second line where I'm deleting the UsedRange on the Data sheet (where the new data will be put). Should I remove that line of code and replace it with something that "clears" the cells and doesn't delete them?
Thanks,Please Login or Register to view this content.
Try changing that to:
Please Login or Register to view this content.
Awesome, I think it is working! I had to define my dynamic range in VBA before the pivot table refresh, otherwise the function resets with REF# for the ranges. Included this:
Now I can just use the pt refresh and it goes SO much faster! THANKS SOOOO MUCH MAN!Please Login or Register to view this content.
Glad to help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks