Vanishing Pivot Table

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Greetings All,

I would appreciate help with the following problem. I am running
Excel 97 under Windows 2000.

The situation:
I have 2 pivot tables (lets call them A and B) on one worksheet. Both
reference the same worksheet table as their sources. Both have the
same 2 page fields, and one simple datafield each. Pivot table A uses
a single column field (i.e., horizontal orietation) while B uses a
single row field (giving it a vertical orientation. A is higher on
the sheet than B.

The problem:
I am using VBA code to first clear and then re-make pivot table B with
exactly the same position and structure as before. It works, and I
have verified that my clearing procedure only affects B. But after
the following line of code, pivot table A vanishes. I have isolated
the disappearance to this statement:

'Remake pivot table B. ======================================
wsReport.PivotTableWizard SourceType:=xlDatabase, SourceData:=strSrce,
TableDestination:=rngPTLocum, TableName:=strPTName,
ColumnGrand:=False, HasAutoFormat:=False, RowGrand:=False
'end of code snippet. =======================================

Before this single line is executed, pivot table A is all present and
accounted for. After stepping this single line pivot table A is GONE
(while B is created nicely as expected).

Please note:
rngPTLocum is the single cell reference for B's position, and is
several lines below the last row of A. A and B coexist happily before
this automated re-build runs. There is no impinging of page fields on
data fields or vice versa between the two pivot tables.

This is a major puzzle for me and any knowledgeable help will be
appreciated.

Michael
 
Unless there's a real reason to build the tables with VBA,

I've found following trick saves a lot of work:
base your Pivot on a defined name rather then point o the range
directly.

You can manipulate the referstorange of the Name object
of use Offset function to make it dynamic.

Then a simple refresh will get latest data including added rows.


BTW.. the reason for your problems is that the Pivots probably share the
same PivotCache object. When you change the connectstring on the second
Pivot, it kills the cache which is also used for numero1.

Tip:
Manipulate the (shared) pivotcache object then refresh the PivtoTables.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Hi and thanks,

Yes, this must be automated. Fyi, this problem occured when table B
pointed to table A as source (I answered "yes" to the dialog asking if
I wanted to base the new table on the existing one, or
sourcetype:=xlPivotTable, sourcename:="A"), as well as when B was
created as "independent" (sourcetype:=xlDatabase,sourcename:="<named
range>"). Does this mean that table B is never independent regardless
of how I respond to the dialogue?

Can you give me an example of how one might manipulate the pivotcache
in a situation like this?

Michael
 
Back
Top