Alright, you excel and VBA geniuses. I call upon your wisdom! This is a tough
question, and I've done a bit of homework already trying to fix it, so I'll
be as detailed as possible.

My problem is simple. When I update a pivot page field, it takes about 15
minutes to update. Ouch!

Here's a description of the document, after that, I'll describe what I've
tried.

I have document called invoice generator. It contains a total of 5 pivot
reports referencing a data table from an outside sheet. The first pivot table
informs the cover sheet, which is used as a monthly invoice for clients. the
others are the same data displayed different ways as "supporting data".

There are two simple VBA sequences. One I recorded with the macro recorder.
All it does is copy the whole workbook and pastes back the values (getting
rid of all links and formulas) and then saves it as a specified name in a
specified folder. I call the sequence exporting the invoice, although
strictly speaking that is not entirely accurate.

The other was code borrowed from contextures at
http://www.contextures.com/excelfiles.html#Pivot
combining PT0016 and PT0015.

What it does is sets two control cells on the cover sheet. I select the
client and the date, and all the pivot tables in the whole workbook update
the page fields to those values, so the invoice is automatically generated
(hence the name).

The source data that informs the pivot tables is huge. Each month, I add
roughly 15-25,000 rows of data, 27 columns wide, all of which is static data.
Not a single formula. Obviously, it did not take long to run out of room on
the datasheet, so each month I move the oldest month of data off the sheet,
and paste the new data to the bottom of the sheet.

My suspicion is that the pivot table cache is somehow piling up and storing
all my old, now-not-even-in-the-source-data data.

So I looked and looked, and found several hot topics. The first was of
course, the issue of volatile functions. This is not the issue. Recalculating
only takes a few seconds. The slowness is only happening when I pivot.

I thought of pivot cache because the size of the file was getting unruly, at
about 89MB. So I looked around and discovered I can share cache for all the
pivot tables. First this was attempted using VBA code from he
http://www.contextures.com/xlPivot11.html

It didn't make much difference, so I then used the piviottable wizard to
make each of the pivot tables use pivot table 1 as a source. This reduced my
sheet size down to 11MB, but it still performs slowly.

I then tried to unchecked the option to save data with table layout, and
checked refresh on open. I thought that refreshing each time I open the book
may clear whatever is buggering up the workbook. The result was a file size
of only 1904KB, but alas, the pivot page update still takes 10 minutes.

Is there something in any of the code theory that raises red flags (without
having actually seen the code)? I can paste some of it here, but I don't know
how much detail is necessary to provide input. Contextures has been pretty
reliable, so I don't suspect the VBA, although VBA may provide a solution if
I can identify what's slowing me down. I am sure it's something with the
pivot tables, but I can't find any details on how to monitor/troubleshoot
memory/cache/pivot table problems like this yet.

Any comments? More information needed?