Microsoft Office Tutorials and References
In Depth Information
CREATE A PIVOT TABLE FROM DATA IN MULTIPLE WORKSHEETS
CREATE A PIVOT TABLE FROM DATA IN
MULTIPLE WORKSHEETS
Challenge: You have more data than will fi t on a single worksheet. You would
like to create a pivot table from the data spread across multiple worksheets. The
Multiple Consolidation feature only works when your data has a single column
of text labels on the left with additional numeric columns to the right. You’d like
to be able to grab similar data from multiple worksheets and summarize it in a
pivot table.
Background: Fazza from Perth, Australia, posted a remarkable bit of code in
2008 that allows you to build a pivot cache from multiple worksheets or even
multiple workbooks. Amazingly, the pivot cache is stored with the workbook, so
you can effectively build a report from more than 65,536 rows in Excel 2003.
Solution: The solution here involves building a SQL statement to grab data
from each worksheet into an array. You then merge the worksheets into a
single recordset. You open a new workbook and create a pivot table to an
external dataset—in this case, the recordset you just created. The result is
a blank workbook with a blank pivot table and the cache stored in memory
behind the scenes.
Breaking it Down: You create code that sets up an array of SQL statements.
The complete code is as follows:
Sub BuildPivotCache()
Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wbkNew As Workbook
Dim wks As Worksheet
With ActiveWorkbook
ReDim arSQL(1 To .Worksheets.Count)
For Each wks In .Worksheets
i = i + 1
arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"
Next wks
Set wks = Nothing
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open Join$(arSQL, " UNION ALL "), _
Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
 
 
Search JabSto ::




Custom Search