Archive for May, 2010

After taking over maintenance of an Excel application, the first problem I encountered was that the app was extremely “Pivot Table Heavy”. The original developer needed to take snapshots of a data set, sliced and diced almost 50 different ways (49, to be exact). He accomplished this by creating 49 Pivot Tables on a worksheet, and then having the main report tables (on another worksheet) reference each of these Pivot Tables. I just didn’t know where to begin pointing out the wrong-ness of this whole approach. The immediate problem was that yet another report table was being added, thus necessitating the addition of yet another Pivot Table. But we found that any modifications made to the existing Pivot Table worksheet would cause the application to crash. There was no time to re-write the entire application from scratch.

My first thought was to whittle the data worksheet down to just ONE Pivot Table. The Pivot Table filters could be manipulated in VBA code and a snapshot created after each filter refresh, copying the data to the same location as the current 49 Pivot Tables. This would eliminate the need to change anything on the main reporting worksheet because those tables would still reference the exact same locations where the 49 Pivot Tables were except now it would not be 49 Pivot Tables, but 49 standard data ranges that had been copied from the “master” Pivot Table. Thus making the workbook less “Pivot Table Heavy” and hopefully more stable.

I could handle writing the code to manipulate the filters, etc., using VBA without a problem, but I needed a clean way to copy just the column headers, row labels and data from the Pivot Table (i.e. NOT the page or report filters). So, what is the best way, using VBA, to read all of the visible data in a Pivot Table? I needed to just be able to grab all the data I wanted from the Pivot Table and copy it somewhere else. There could be 1 row or 1,000 rows in the pivot table. In either case, I just want to grab it all and copy it. The GetPivotData function provides copying on a cell by cell basis and so was unsuitable for this application.

After some experimentation, I discovered the TableRange1 method of the PivotTable object.

Let’s begin by creating a new macro-enabled workbook and call it PivotCopyTest.xlsm.

Rename “Sheet1” to “Stores“, and populate it with the following test data, borrowed from a Microsoft “Contoso” test database. (Note that this is an admittedly dopey example, and not the data I was actually working with, but it’s sufficient to demonstrate the main point of this post):


Raw Data

Source Data for Pivot Table

Next, create a Pivot Table on a new worksheet as follows (Excel 2010 instructions; if you’re using 2007, this may differ slightly):

1. Select one of the cells in the source data table.

2. Click on the “Insert” ribbon tab, then click the “PivotTable” button. This will bring up the CreatePivotTable dialog box. Make sure that the selection range is specified properly, and that the Pivot Table will be created on a “New Worksheet”. Click the “OK” button to create the Pivot Table.

Pivot Table Creation Dialog

Pivot Table Creation Dialog

3. Pivot Table should be defined as follows:

  • Report Filter = GeographyKey
  • Column Labels = StoreType
  • Row Labels = StoreName
  • Values = Sum of Sales

The PivotTable Field list and final Pivot Table appearance are shown below:

Field Layout and Final Appearance

Field Layout and Final Appearance

Open the VBA Editor, insert a new Standard Module and add the following subroutine:

Public Sub SelectPivotData()
'Copy the column headers, row labels and data from a Pivot Table.
Dim pvtTest As PivotTable

Set pvtTest = Sheet1.PivotTables(1)
With pvtTest.TableRange1
    'Output the Address of the TableRange1 range, just to confirm that it's grabbing the data we want
    ActiveSheet.Range("G1") = .Address
    'Now copy the data to a new location
    .Copy Destination:=Range("G5")   
End With
End Sub

That’s all there is to it. Run this subroutine and you’ll see that this strips off the report field filters and leaves me with just the data I want.

For anyone who is interested, there is also a TableRange2 property which returns all of the visible rows in the pivot table, including the page/report fields. And, as I found out from Andy Pope (Microsoft guru), there is also a DataBodyRange property, which excludes column headers and row labels, leaving just the actual data values. Just substitute either of these values for the .TableRange1 in the code above to test them out.

Go figure. After many years working in Excel, I never heard of “TableRange1”, “TableRange2” or “DataBodyRange”.  Always an adventure…



Read Full Post »