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.

Leave a Reply

Your email address will not be published. Required fields are marked *