Creating multiple pivot tables from same cache

  • Thread starter Thread starter Fred Smith
  • Start date Start date
F

Fred Smith

I have a macro which should create three pivot tables from the same source
data. I can manually create all three sans problem. However, when I execute
the code, I get the first two tables, but on the third table, Excel stops
with error 1004 on the following statement:

Set PT = PTCache.CreatePivotTable(TableDestination:="", _
TableName:="AssetLevels")

This is exactly the same statement which starts off the creation of the
second table (other than changing the TableName).

I'm using XL2002, but I need it to work in XL2000 as well.

What am I doing wrong?
 
untested but it is probably the "destination" argument. If you leave it blank it will try to create a new sheet - once but if you do it multiple times it may be trying to overwrite the 2nd pivottable. Instead of leaving it blank, you could us

sheets.add after:=sheets(sheets.count

Set PT = PTCache.CreatePivotTable(TableDestination:=sheets(sheets.count).range("A1"),
TableName:="AssetLevels"

or similar
 
That was it!

For some reason, Excel will, when given a blank destination, create a first
pt, and a second pt, but won't create the third.

Oh well, live and learn.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


xlbo said:
untested but it is probably the "destination" argument. If you leave it
Click to expand...
blank it will try to create a new sheet - once but if you do it multiple
times it may be trying to overwrite the 2nd pivottable. Instead of leaving
it blank, you could use
 
Back
Top