Microsoft Office Tutorials and References
In Depth Information
CREATE A PIVOT TABLE FROM DATA IN MULTIPLE WORKSHEETS
The code then builds a new recordset that unions all the queries from Figure
83. This recordset will contain all records from all worksheets.
Where is the recordset saved? It is simply resident in memory as the object
Later, the code adds a new workbook and uses the recordset as the source for
an external pivot cache:
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
You end up with a new workbook that appears to be blank. An empty pivot
table is in cell A3. As shown in Figure 84, the pivot table ﬁ eld list contains a list
of all ﬁ elds from your worksheets.
Figure 84. Although the workbook contains no visible data, the
i elds are in the pivot cache.
From this point, you can drag ﬁ elds into the pivot table.
If the pivot cache contains fewer records than there are rows in your worksheet,
you can try double-clicking the Grand Total cell. You then see the data that is
stored in the pivot cache.
Note: It is interesting that the workbook containing the data stored in the pivot
table cache is much smaller than the original workbook. Excel doesn’t have
to store fonts, styles, formulas, etc.