Trying to refresh just one pivot table

[PeteG]

Hi,

I am currently working on a workbook containing many pivot tables; approx. 5 each on 5 separate worksheets. Between them, they all pretty much use one of two ranges as the data source.

Each worksheet has code to refresh all the pivottables within it, on the worksheet activate event. I am trying to come up with a way to ensure that, when each pivot table is refreshed, it will not overwrite any other pivots or other data that is located nearby. The way I thought to do that was to 1) Add a new temporary worksheet, 2) iterate through all pivot tables on the worksheet. One by one, do this: 3) Move the pivot table to the new sheet 4) Refresh it 5) Move it back to its original location but check that the new dimensions of the pivot table do not overwirte any data (using counta function) 6) If it would overwrite, test moving the destination one column to the right, test again, and so on until it will fit, then paste it.

Generally, this appears to be working except for one (showstopping!) issue at point 4. When I refresh a table on that temporary worksheet, any other tables back on my original worksheet that use the same data source are also refreshed immediately. This scuppers my plan, as the whole point of what I am doing is to check that the new size of each pivot table does not overwrite anything, and if it would do, move it accordingly. It appears not to matter whether I use .RefreshTable or .PivotCache.Refresh on my pivot table object, the behaviour is the same.

So, my question is, is there a sure-fire way to have control over just one pivot table at a time, to be able to refresh it without also refreshing other tables that share the same data source or cache?

Thanks for any help.

 

[SkipVought]

Hi,

Several thoughts arise.

You could use MS Query to analyze the source data thus determine the number of columns.

But I'd be inclined to put the 25 on separate sheets and use the CAMERA feature to DISPLAY each PT on the appropriate sheet, at the appropriate place, with the appropriate size. As columns change in the various PTs, you could maintain the display sizes, thus compressing the data in each display, or reposition the displays the fit the new sizes.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PeteG]

Hmmm, thanks for the suggestions. I only recently found out about that camera feature, might give that a go! Am I right in thinking that you don't consider it a trivial thing to be able to control the refreshing of the pivot tables?

 

[SkipVought]

Your really refreshing a PivotCache which can have multiple associated PivotTables.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]