Solved: Pivot Table(s) - Refresh

[Philcjr]

Hello All - from rainy NJ

I am interested in have some VBA code to update all Pivot Tables within my workbook.

Would I loop through all worksheets looking for Pivot Tables?
-or-
Would I loop through all worksheets looking for worksheets name that starts with "Pivot"?
-or-
Would I loop through all the Pivot Tables?

Currently, I have PivotTables1-6 on three different worksheets (named Pivot Table, Pivot Table (2), Pivot Table (3))

Any direction would be great

[Bob Phillips]

PTs are worksheet objects, so you have to have two iterations

[vba]

Sub GetPTs()
Dim sh As Worksheet
Dim pt As PivotTable

For Each sh In ActiveWorkbook.Worksheets

For Each pt In sh.PivotTables

Debug.Print pt.Name
Next pt
Next sh
End Sub
[/vba]

[Philcjr]

Bob,
Thanks! This is perfect... here are my tweaks.
[vba]
Sub PivotTableRefresh()
Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws

End Sub[/vba]

[Bob Phillips]

Whoops! Didn't take note of the subject :-)

[JWhite]

This may not be relevant in your case, but if any of these pivottables share the same pivotcache you only need to refresh one of the pivottables and all the rest are refreshed automatically.

[xluser2007]

This may not be relevant in your case, but if any of these pivottables share the same pivotcache you only need to refresh one of the pivottables and all the rest are refreshed automatically.

Hi All,

Based on the above, is there a VBA method of determining which pivotcache each pivottable is using?

Or

Is there a way to refresh all the relevant pivotcaches in a workbook only? This would mean that all pivottables are refreshed automatically based on JWhite's comment above.

This is useful for me as I have many workbooks where I refresh the pivottables with a code similar to that posted by Bob (xld).

regards

[JWhite]

In my case, I'm always dealing with one or more pivottables from the same pivotcache. But I think you can look at the "pivottable.cacheindex" for each pivottable, which gives you the index number of the pivotcache for that pivottable. Pivotcaches don't have names so you can only query the index # of the cache.

This looks awkward to me but it's the best I can come up with. It looks like you would have to look at the collection of sheets and then the collection of pivottables in each sheet. Every time you refresh a pivottable, you would have to store the cacheindex of that pivottable. For every pivottable you would have to check if you had already refreshed that cacheindex. It wouldn't be that much code but you would think they would have a collection of pivottables for every cache and, as far as I know, they don't.

If you can come up with something better, I would like to know about it.

[xluser2007]

In my case, I'm always dealing with one or more pivottables from the same pivotcache. But I think you can look at the "pivottable.cacheindex" for each pivottable, which gives you the index number of the pivotcache for that pivottable. Pivotcaches don't have names so you can only query the index # of the cache.

This looks awkward to me but it's the best I can come up with. It looks like you would have to look at the collection of sheets and then the collection of pivottables in each sheet. Every time you refresh a pivottable, you would have to store the cacheindex of that pivottable. For every pivottable you would have to check if you had already refreshed that cacheindex. It wouldn't be that much code but you would think they would have a collection of pivottables for every cache and, as far as I know, they don't.

If you can come up with something better, I would like to know about it.

Hi JWhite,

Mate, I'm quite a newb to all this pivottable, pivotcache stuff, so not sure if I can directly add anything further to your knowledge.

Just to describe clearly what I do. I have in one workbook 3 dynamic named ranges data1, data2, data3.

Many pivottables in the same workbook reference these 3 ranges.

I just use a code similar to the one asked by the OP to refresh the each pivot-table in the workbook. This is reasonably fast, but if you or anyone else had any insights on how to adapt your suggestion on pivotcaches to tackle this pivot-table refreshing problem, I was interested.

Would my dynamic named ranges be the pivotcahes?

Still not sure exactly how using pivotcahes would factor into my example. Though it does sound interesting.

Cheers,

[JWhite]

I'll tell you what I know. I work with pivottables all the time but I only work with external data. I often have dozens of pivottables in one workbook, all using a single source of external data which may be hundreds of thousands or, in one case, up to 3 million rows. A personal note: I noticed you used "mate", which made me nostalgic. I just moved back to the US last year after 5 years in Australia and I miss it a lot.

Anyway, on to pivotcaches. Every time you create a pivottable it also creates a pivotcache, which is an intermediate hidden cache which actually holds the data. If you're getting data from another worksheet, Excel copies the data into a pivotcache. The pivottable uses the cache, which is obvious because you can change the data on the source worksheet and the pivottable doesn't change - until you refresh it, at which time it re-copies the worksheet data into the pivotcache.

If you want to create a second pivottable from the same worksheet, you can define a whole new pivottable. In that case, Excel creates a new pivotcache even though it's the same data. Or you can copy the first pivottable to a new location (there's another way to create a second pivottable linked to the same pivotcache, which you can find in VBA Help, but copying the pivottable is usually easier).

I've created dozens of pivottables from the same pivotcache by copying the pivottables. It's NECESSARY for me because the data source is so huge. But it also speeds things up enormously because I only have to refresh one pivottable (any of them) and ALL of them refresh. It may not be worth the trouble in your case if you're not dealing with a lot of data.

Pivotcaches belong to workbooks since the same pivotcache can support pivottables on different worksheets. That makes it awkward to work with them. They don't have names and the only way you can relate them to the pivottables is through the "pivottable.cacheindex" property.

This is probably more than you want to know but you can read a lot and still you'll find that there are some tricky aspects to multiple pivottables from one pivotcache. Feel free to ask if you want to know more. I'll do the best I can but there are a lot of people with more experience than me.

[xluser2007]

I'll tell you what I know. I work with pivottables all the time but I only work with external data. I often have dozens of pivottables in one workbook, all using a single source of external data which may be hundreds of thousands or, in one case, up to 3 million rows. A personal note: I noticed you used "mate", which made me nostalgic. I just moved back to the US last year after 5 years in Australia and I miss it a lot.

No worries mate . Glad it brought back good memories.

Anyway, on to pivotcaches. Every time you create a pivottable it also creates a pivotcache, which is an intermediate hidden cache which actually holds the data. If you're getting data from another worksheet, Excel copies the data into a pivotcache. The pivottable uses the cache, which is obvious because you can change the data on the source worksheet and the pivottable doesn't change - until you refresh it, at which time it re-copies the worksheet data into the pivotcache.

If you want to create a second pivottable from the same worksheet, you can define a whole new pivottable. In that case, Excel creates a new pivotcache even though it's the same data. Or you can copy the first pivottable to a new location (there's another way to create a second pivottable linked to the same pivotcache, which you can find in VBA Help, but copying the pivottable is usually easier).

I've created dozens of pivottables from the same pivotcache by copying the pivottables.

This is really insightful for me, especially the last point.

My data is usually contained within the workbook (not external data as you seem to use).

I usually end up copying the same pivottables and then adjusting them as required. I once tried to create a pivot table identical to one I already had made in the workbook (i.e. didn;t copy it and adjust accordingly), and it kept popping up with a message to re-link to the same pivotcache as the previous table.

I didn't realise at the time that it was trying to do what you described above, with regards to the pivottables trying to refrence the same pivotcahche for memory efficiency.

Good to know that copying pivottables relying on the same data source is the best way forward.

It's NECESSARY for me because the data source is so huge. But it also speeds things up enormously because I only have to refresh one pivottable (any of them) and ALL of them refresh. It may not be worth the trouble in your case if you're not dealing with a lot of data.

Pivotcaches belong to workbooks since the same pivotcache can support pivottables on different worksheets. That makes it awkward to work with them. They don't have names and the only way you can relate them to the pivottables is through the "pivottable.cacheindex" property.

This is probably more than you want to know but you can read a lot and still you'll find that there are some tricky aspects to multiple pivottables from one pivotcache. Feel free to ask if you want to know more. I'll do the best I can but there are a lot of people with more experience than me.

Thanks, this thread has already been helpful.

I will keep you posted with further queries.

Cheers,