Clearing Pivot Table from Memory

[MFRASER]

I am having a Memory issue with my pivot tables and I found the following
snippet from a knowlege base article. How do I accomplish this in code.

Ok I found an article in the knowledgebase but I don't understand how to do
the following. Can I have multiple WorkBooks inside of Excel?

PivotTable Wizard Step 1: Where Is the Data That You Want to Analyze?
When Excel creates the cache for a PivotTable, both the original data and
the cached data may be kept in memory, depending on the data source option
that you select. If your data source is large or if you are creating several
PivotTables from separate data sources, these multiple copies of data may
use up your computer's available memory, which will adversely affect the
performance of Excel. To save memory, you can use a closed original data
source or a single data source when you create multiple PivotTables.

The memory usage implications for each of the data source options in step 1
of the PivotTable Wizard are described in more detail in the following
sections.

back to the top
Microsoft Excel List or Database, or Multiple Consolidation Ranges
When you select Microsoft Excel list or database or Multiple consolidation
ranges as your data source, if this data is in an open sheet, two copies of
the data are kept in memory when Excel creates the PivotTable. To conserve
memory, close the workbook that contains your original data, and create the
PivotTable in a separate workbook. This way, only the cached data is kept in
memory.

back to the top

 

[MFRASER]

Ok. I am trying to link one pivot table to another pivot table but the
following code fails, any ideas?

Private Function AddPivotTable(rst As ADODB.Recordset, sheet As Worksheet,
DataSheet As Worksheet, IsChild As Boolean) As PivotTable
Dim pTable As PivotTable
Dim RangeName As String
Dim intRowIndex As Long
Dim intColIndex As Long
Dim TargetRange As Range
Dim pCache As PivotCache

On Error Resume Next
'Test if pivot table already exists
Call GetDimensions(intRowIndex, intColIndex, DataSheet)

Set TargetRange = DataSheet.Cells(intRowIndex, intColIndex)
RangeName = "" & DataSheet.Name & " !R1C1:R" & intRowIndex & "C" &
intColIndex
If ActiveWorkbook.PivotCaches.Count = 0 Then
Set pTable = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:="" + RangeName +
"").CreatePivotTable(TableDestination:=sheet.Range("A8"),
TableName:="PivotTable1")
Else
sheet.Activate
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
_
"PivotTable1", TableDestination:=" & sheet.Name & ""!A8",
TableName:="PT" & sheet.Name
Set pTable = sheet.PivotTables(1)
End If
Set AddPivotTable = pTable

First thing, you should move the database list to another Excel file, then

have your PivotTable reference it. That will eliminate one instance of the
database in memory.

For Each pivottable, do you use the database list as your source? If so,

that's 10 more instances of the database. Each PivotTable needs a
PivotCache to use. If you want to make it more efficient, you need to make
each PivotTable reference another PivotTable using the database list. That
will make one instance of the PivotCache.

The problem is, all the PivotTables tied to the same cache creates a major

problem - all the PivotTables must look exactly the same. If you change one
PivotTable, it changes them all. Which kind of makes sense, why do you
need multiple pivottables if you can easily change the format of the
PivotTable to demonstrate something?