Hi
Copy the Code from my previous posting
Alt+F11 to invoke the
VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel
To use
Select cell within your first PT
Alt+F8 to bring up Macros
Highlight the macro name
Run
It will cycle through each of the PT's in turn, setting the cache that each
uses to be that from the first PT, rather than you doing each one manually.
Save and Close your workbook and the file size should be reduced.
--
Regards
Roger Govier
"1genxer" wrote in message
...
Can you tell me exactly what this macro does? I am not very educated in
code
like that.
"Roger Govier" wrote:
Hi
From Debra Dalgleish's "Excel Pivot Tables Recipe Book"
Sub ChangeAllPivotCaches()
Dim pt as PivotTable
Dim ws as Worksheet
On Error Goto err_handler
For each ws in ActiveWorkbook.Worksheets
For Each pt in ws.PivotTables
pt.CacheIndex = Activecell.PivotTable.CacheIndex
Next pt
Nex ws
Exit Sub
err_Handler
Msgbox "Cache Index could not be changed"
End Sub
Place cursor in any cell of the first PT you created, then run the macro
--
Regards
Roger Govier
"1genxer" wrote in message
...
Is there a way to do that outside the Wizard as my tables are already
built?
I really don't want to have to build them from scratch again.
"Ron Coderre" wrote:
Each new pivot table creates a hidden copy of the source data. That's
how
you
can keep working with the same pivot table data eve if the source data
changes.
You have the option to make pivot tables share the same cached source
data
when you create the pivot table.
If you open the pivot table wizard and click the [back] button to Step
1
of
3...
Select the 4th option button (Another Pivot Table report...)
Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the
others.
Does that help?
***********
Regards,
Ron
XL2003, WinXP
"1genxer" wrote:
I have a file with about 8,000 rows of data and about 30 columns. I
have
about 26 pivot tables that are in one of the sheets. The file is
over
13MB.
If I delete the pivots my file size drops to only 6MB.
My question is, A) how much space does a Pivot Table take in a file,
is
it
the full size of the data it refrences? B) Is there a way to reduce
the
amount of file space of the Pivot Table?
Any help would be much appreciated as a 13 MB file is very unwieldy
to
update and manipulate when opened, and the file just gets bigger
every
month.
Thanks!!!