Create A Pivot Table From Data In Multiple Worksheets

April 28, 2021 - by Bill Jelen

Challenge: You have more data than will fit 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:

If the active workbook contains five worksheets, the array might look as shown in Figure 83.

Figure 83. You build multiple SQL statements to grab all the data from each worksheet.

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 variable objRS.

Later, the code adds a new workbook and uses the recordset as the source for an external pivot cache:

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 field list contains a list of all fields from your worksheets.

Figure 84. Although the workbook contains no visible data, the fields are in the pivot cache.

From this point, you can drag fields 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.

Gotcha: Unlike pivot tables built from data in a worksheet, this pivot table does not calculate until you drag one field to the data area. Don’t worry if you drag a region to the row area and nothing happens. As soon as you add Revenue to the data area, the pivot table calculates, and the regions fill in.

Summary: You can create code that grabs similar data from multiple worksheets and summarizes it in a pivot table.

Source: Creating a pivot table with multiple sheets on MrExcel Message Board.

This topic was nominated by Denis Wright (aka SydneyGeek), who builds custom Excel and Access solutions in Sydney, Australia, and loves to solve problems. Among other things, he maintains a website with Excel and Access tutorials; see DataWright Information Services.

Title Photo: Jana Shnipelson on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.