Excel Pivot tables

[Guest]

how do i limit the amount of time it takes to refresh a pivot table from an
excel file that has about 30,000 rows of data? I have about 100 pivot tables
that all feed from the same excel data file and it takes about 2 minutes to
refresh each pivot. Is there a solution out there that could limit the time
of refreshing?

 

[Debra Dalgleish]

What version of Excel are you using?
Did you create one pivot table and then base all the others on that
pivot table?

If each pivot table is based on the source data, and has a separate
pivot cache, that will slow things down. You could use the sample code
here, to set all the pivot tables to the same pivot cache:

http://www.contextures.com/xlPivot11.html

Test on a copy of your workbook, and see if it helps.

 

[Guest]

I'm on Excel 2003. Each pivot table is on a separate worksheet pulling
source data from a separate workbook. (All pivots pull from the same
workbook). How do I write the code in my pivot?

 

[Debra Dalgleish]

Follow the instructions here for copying code to a regular module:

http://www.contextures.com/xlvba01.html

Then, choose Tools>Macro>Macros, select the macro, and click Run.

 

[Guest]

should i be copying this code into the source data file or the file where the
pivots are? Once copying code is done, do i just run the macro?

 

[Debra Dalgleish]

It can be copied into any workbook, even a new blank workbook.
Change the code to match a sheet name in your pivot table workbook.
pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex

The pivot workbook (remember to make a backup copy first) should be
active when you run the macro.

 

[Guest]

I got it! Thank you very much

It can be copied into any workbook, even a new blank workbook.
Change the code to match a sheet name in your pivot table workbook.
pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex

The pivot workbook (remember to make a backup copy first) should be
active when you run the macro.