Solved

Pivot table question

Posted on 2011-09-28
2
185 Views
Last Modified: 2012-06-27
I have 3 pivot tables.  How would I programmatically refresh all 3
pivot tables to where they refresh individually?  As in I want the 1st
one to finish refreshing before the 2nd begins to refresh, and I want
the 2nd one to finish refreshing before the 3rd one begins to refresh?


0
Comment
Question by:tkrpata
2 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
Comment Utility
Does something like the attached code not work for you? Refreshing each table in turn should do so sequentially. If need be, you could put in a wait statement in between each Refresh statement like this one:
Application.Wait (Now() + "00:00:02") 'Waits two seconds

This one statement will also refresh every pivot table in the workbook, but not with any manual control over order or timing:
ThisWorkbook.RefreshAll

Is there a particular reason they need to be spaced out?
With Application.Sheets("Pivot Tables")
.PivotTables("Pivot1").PivotCache.Refresh
.PivotTables("Pivot2").PivotCache.Refresh
.PivotTables("Pivot3").PivotCache.Refresh
End With

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
When the Pivot Tables were created, do they all use the same data source?

When creating a second pivot table using the same data source as a previous pivot table there should have been a message asking if you wanted to use the same data source. By saying No to this message it increases file size, hence why it is offered.

However, if the tables are linked to the same data source, refreshing one table will refresh all of them at the same time. I suspect this is the behaviour that you are seeing here that you want to get away from.

I suspect to get round this you would have to reset the data source for each pivot to be independent.

If this is on a large data source, I am guessing that this behaviour is causing the user's computer to slow/freeze while refreshing pivots whereas if they were done individually it may not cause this.

Thanks
Rob H
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now