Using the same field twice in a pivot table

I answered a post this morning on MrExcel which reminded me of one of the (many) oddities I’ve come across when writing code for pivot tables.

The scenario:

You have a simple one column table:

Header1

Client a

Client b

Client c

Client d

Client e

Client a

Client b

Client c

Client d

Client e

Client a

Client v

Client f

Client a

Client d

Client a

Client c

Client b

Client v

Client f

Client a

Client d

Client c

Client v

Client b

Client f

Client a

Client d

Client f

Client v

Client b

 

You now want to create a pivot table that counts how many times each item appears. So you want the same field as both a row field and data field. This is very simple to do manually – just drag it to both areas – and you end up with what you want:

Row Labels

Count of Header1

Client a

7

Client b

5

Client c

4

Client d

5

Client e

2

Client f

4

Client v

4

Grand Total

31

 

Now, if you record a macro while doing that, you’ll end up with something like this:

Sub Macro2()
'
' Macro2 Macro
'
'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet5!R3C1", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Header1")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Header1"), "Count of Header1", xlCount
End Sub

 

The problem:

If you run this code again (adjusting the Sheet5 name in the code to match the next sheet number), you actually end up with this table:

Count of Header1

31

 

If you step through the code, you will see the field does get added as a row field, but then gets moved to the data area rather than added there as well.

 

The solution:

The solution is actually just to move the line that adds the data field so that it occurs before the code to add it as a row field.

The reason for this is that the data field caption is now “Count of Header1” so a reference to .PivotFields(“Header1”) is not a reference to the data field you just added, and it doesn’t get moved when you set its Orientation.

VBA to change pivot source data

This morning’s post was prompted by a question posted at MrExcel (here) regarding changing the source data for multiple pivot tables to refer to a different worksheet range. The stumbling block turned out to be that when you have multiple pivot tables that use the same pivot cache, you cannot simply change the SourceData property of the existing cache as you can if there’s only one pivot table. The workaround is to create a new cache and then assign that to all the pivot tables:

Sub ChangeCaches(sNewSource As String)
    ' sample to change multiple pivots based off the same Excel Range-based pivotcache
    Dim pc                         As PivotCache
    Dim ws                         As Worksheet
    Dim pt                         As PivotTable
    Dim bCreated                   As Boolean

    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If Not bCreated Then
                ' this only adds a new cache on the first run through
                ' on subsequent passes, the pivot tables are simply assigned to the new cache
                ' if multiple caches are desired, simply repeat this part for each pivot table.
                pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                          SourceData:=sNewSource, Version:=xlPivotTableVersion14)
                Set pc = pt.PivotCache
                bCreated = True
            Else
                If pt.CacheIndex <> pc.Index Then pt.CacheIndex = pc.Index
            End If
        Next pt
    Next ws
End Sub

Hopefully that may prove useful to others.

 

Note: trying to create the PivotCache before the loop and then assigning it to the pivot tables doesn’t work for some reason. The cache ends up with an Index of zero and is unusable.