XL2007 Pivot Table causes code to run slow?

[michael.beckinsale]

Hi All,

I have a workbook into which l import a bunch of data. The objective
is to then construct a data source for a specific pivot table.

Running the code to import all the data & construct the data source
takes approx 5 minutes without the pivot table.

Running the same code with the pivot table present takes > 20 minutes
(got fed up & cancelled)

I have done all the usual things, turned off autocalc, screen updating
etc.

If l delete the sheet containing the pivot table and re-run the code
it returns to approx 5 minutes.

Is there some sort 'autocalc' within the pivot table that needs
turning off?

All help gratefully appreciated

Regards

Michael

 

[royUK]

Can you not delete the PivotTable & re-build it with code

 

[Barb Reinhardt]

It would help if we could see your code.

 

[michael.beckinsale]

Hi All,

Yes l could delete the pivot table and rebuild with code but that
seems to be very inelegant.

It seems to me that the pivot table is 'constantly' attached to the
data source and even with auto calc set to manual wants to refresh
each time a cell in the data source is changed. It seems inconceivable
that this cant be turned off somehow?

Barb - The code to import the data is massive and does not refer to
the pivot table in any way, however it does delete the pivot table
data source range and re-builds it, hence my comments above. The data
source range is approx 15000 rows by 65 columns and requires lots of
loops & lookups, hence the 5 minutes running time. I am currently
looking at ways of reducing this but time restraints neccesitate that
l at least get an operating version of this quickly (like today!)

I really would appreciate any help on this as l am under pressure to
get this working quickly.

Regards

Michael