3 Pivot Tables

[Todd Huttenstine]

Hey guys

Before I ask this question I know Im probably going to get
the answer that I need more memory but I think there has
to be another way...

I have 3 pivot tables on 1 worksheet. They all work
perfectly. Heres the problem. Lets say I change a column
field value from the drop down on any pivot table, then I
change another page field value from the drop down on a
second pivot table, and then I change another page field
value from the drop down on the 3rd pivot table. It does
not matter in which order of pivot tables I do this with,
I will get an error on the 3rd pivot table. It says "Not
enough memory" and "continue with undo"

I believe this can be corrected if I could somehow clear
the memory in use. Maybe clear the memory after each
pivot table is calculated??? What is a code to clear
memory or is there anything else that will correct or help
this?


Thank you
Todd Htutenstine

 

[Toby Erkson]

Well, from the sound of it, you can simply continue it's just that you won't
be able to Undo anything, that's all.

Do the three tables each pull from a different data source or do they use the
same one?

 

[Todd Huttenstine]

They all pull from the same source

-----Original Message-----
Well, from the sound of it, you can simply continue it's just that you won't
be able to Undo anything, that's all.

Do the three tables each pull from a different data source or do they use the
same one?

--
Toby Erkson
Oregon, USA
Excel 2002 in Windows XP




.

 

[Toby Erkson]

Did you create them each as a separate query, using the Pivot Table Wizard?
If so, I would recommend you select the first table (assuming it is the one
you created first) by right clicking anywhere within it, click Select, then
click Entire Table. Copy the selection using Ctrl C and paste (Ctrl V) it on
the same spread sheet, like below your #2 table (not on top of it!). Doing
the same thing, copy the FIRST table and paste it under your third table.
Now, you can fill in the fields in the 'new' tables so they match the fields
in the tables they are under. When done you can delete the 'old' #2 and #3
tables and move the 'new' tables in their place. This way if you have any
charts that point to these locations they won't get messed up.

Each table now only pulls -- and saves -- data from one source. This can
greatly reduce memory consumption and each table can still be individually
manipulated.

 

[Todd Huttenstine]

Ok I did what you said and it worked. It reduced the size
of my worksheet from 56MB down to only 34MB. That is
great. Now it works fine.


Todd

 

[Toby Erkson]

Coolio.

Excel's way of telling you AFTERWARDS that the data source is already present
is a PITA...why don't they let you simply select it first? And when you get
several tables, coming from different sources, that window listing the various
sources isn't that helpful. It can take hours, and a lot of memory, if you
mess up like I originally did and pull data from a pointer to a pointer to a
new data source that has the same info as another table (you get the idea). I
found copying/pasting a much saner method