I developed a program wherein I create 2 pivot tables and 1 chart
The two pivot tables are independent of one another, but the chart is related to the second pivot table
The pivot tables and charts all reside on the same Excel worksheet

The program proceeds to delete the 1st pivot table and reconstruct the pivot table in the same location
The program sequentially tries to delete the 2nd pivot table and its chart, then reconstructs the two elements back into the same sheet. This is a refreshing action in the program

The problem I run into is: upon trying to clear the 2nd pivot table, I get a catastrophic failure (i.e. frozen Excel).
I use the pivotTable.TableRange2.Clear( ) call.

In addition I noticed the following problem when I do the following combinations of 2 reports, these are the results

1st report as Pivot Table, 2nd report as Pivot table and Pivot chart --- FAILURE on refresh of both report
1st report as Pivot Table, 2nd report as Pivot Table only --- GOOD on refres
1st report as Pivot Table and Pivot Chart, 2nd report as Pivot Table --- GOOD on refres
1st report as PivotTable and Pivot Chart, 2nd report as Pivot table and Pivot chart -- FAILURE on refres

So basically when the 2nd report contains both a pivot table and pivot chart and we're trying to reconstruct the contents, there is a problem..

You may have your own reasons for the setup you describe, but there are
a couple of things I would question :-

1. I do not see any reason to completely reconstruct a pivot table. I
do reporting on a daily basis and find the simplest and quickest method
is to copy the workbook, replace the base data with new, and refresh
the pivot table(s). These then feed into the formatted report.

2. I am not surprised you are having problems with 2 pivot tables and a
chart on 1 worksheet considering that the sizes can change considerably
with new data. Why not 3 sheets ?

3. I am familiar with the need to supply different sheet sets to
different people. In this scenario I , once again, copy the whole
workbook - which contains all sheets likely to be required - and
*remove* the unwanted ones. This also means that I do not have to keep
multiple copies of reports. Once I have emailed the particular version
required I can delete that workbook, still retaining the master.


---
Message posted from http://www.ExcelForum.com/