Multiple Pivots Refresh

B

brendanolear

Active Member
Joined
Apr 26, 2008
Messages
366
I have a workbook that contains 20 pivots - one to each tab - the original pivots covered the same data range A:Z previously on refreshing one pivot the other 19 would auto refresh also.

I have had to extend the range to A:AC across all pivots - now when I refresh one pivot the others remain unchanged and I have to manually refresh each individually.

Have I done something wrong when amending range on all pivots? Is there Is there a workaround?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
....should also add, if unrecoverable - how do I avoid this situation in the future as I fear I may have to rebuild.
 
Upvote 0
Bump because I'm having what might be the same problem, although it's not so much on the refresh (as I tend to have several tables on the same sheet), but on the change of source data.

In 'old Excel' you had the option to base a second pivot table on same source as Pivot Table 1, now it would seem that you define the source data for each pivot table separately and so if your source data range changes you have to update the range in each table separately.

A work round would be much appreciated as I often end up missing out one of my tables when the source range needs changing and it isn't exactly easy to spot ...
 
Upvote 0
Assuming you are using 2007, press Alt+DP and you will get the old Wizard dialog up. Stepping back through that will allow you to choose to base the table off another pivot.
 
Upvote 0
If your pivot source is always going to be on the same sheet, you might want to use a dynamically defined named range for it. See here: http://www.contextures.com/xlNames01.html

This would be a typical named range that counts rows to determine its height (as in the link) and also counts columns to determine its width:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

...you could name it pivot_data, and then for your pivot source just type in pivot_data. Then you never need to update the range.

For refreshing all of the pivots in a workbook, you can run this code (which I keep in my personal book, you may want to do so also):

Code: 
Sub A_Refresh_All_Pivots()
Application.ScreenUpdating = False
Dim pc As PivotCache
'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
    pc.MissingItemsLimit = xlMissingItemsNone
    pc.Refresh
Next pc
Application.ScreenUpdating = True
MsgBox "Finished refreshing the pivot table(s) in this book!"
End Sub

Hope that helps.

Tai
 
Upvote 0
Rorya - thanks for reply - I am using 2003 - and all historic tables have been amended to new range - can you please advise how I base them on an existing pivot?

I have seen this option when creating new pivots but unsure how to do on existing.
 
Upvote 0
You should be able to just right-click them, choose Wizard... and then step Back to step 1 of the dialog.
 
Upvote 0
Thank you Rorya - that worked perfectly - another one to remember.
thanks also taigovinda - I shall attempt on sample workbook
 
Upvote 0
Thanks chaps

rorya
The Alt DP trick works fine if I'm setting up a new book/set of tables, but if I select Pivot Table 2 and step back then forward at no point do I get asked the vital question about the source data being the same as that for Pivot Table 1 (even though the ranges in both cases are the same).
So, as far as I can see, this means the relevant workbooks need to be set up again - still a much smaller task than updating every time, but somewhat tedious nonetheless - or am I wrong?

tagovinda
My source data doesn't go down to the end of the table, so the range is not quite that easy to define, although I suspect a non-elegant workround for that would be possible.
Like the idea of making them refresh en masse. Thanks for that one.
 
Upvote 0
How far back did you step?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Manual Recalc on Refresh All?
Replies
1
Views
195
jkpieterse
jkpieterse
F
  • Question
Pivot table - refresh all - error 'reference isn't valid' - find the route pivot table causing the issue
Replies
2
Views
600
Alex Blakenburg
A
A
  • Question
Pivot Table VBA Help
Replies
0
Views
435
ashley1984
A
A
  • Solved
Pivot Table Slicers mystery!!!
Replies
9
Views
876
adambc
A
C
  • Question
VBA to Refresh Pivot Table
Replies
5
Views
1K
dragontbone
D

Forum statistics

Threads
1,215,338
Messages
6,124,358
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top