PivotTable VBA code not working as expected. .Caption field property ignored?

[TomCon]

In the following code, it seems that the setting of the .Caption property is being ignored by Excel.
The comments in the code explain what is happening in the workbook.

    ActiveSheet.PivotTables(TabNam).AddDataField ActiveSheet.PivotTables( _
        TabNam).PivotFields("Net"), "Sum of Net", xlSum
'    Range("BX3").Select
    With ActiveSheet.PivotTables(TabNam).PivotFields("Sum of Net")
        .Caption = "Cnt"
        .Function = xlCount
        .NumberFormat = "0"
    End With
'The above adds the field 'Net' from the data range and as usual it defaults to using Sum as the Function
'the the With above attempts to change the Function to Count, applies a format, and changes the caption.
'In the table on the sheet, the Function and Format are correctly changed, but the caption is not changed to the assigned Caption.
'The caption on the sheet is 'Count of Net' which is the default caption for the Count Function.
'It seems as though the .Caption assignment is ignored.
    ActiveSheet.PivotTables(TabNam).AddDataField ActiveSheet.PivotTables( _
        TabNam).PivotFields("Net"), "Sum of Net", xlSum

    With ActiveSheet.PivotTables(TabNam).PivotFields("Sum of Net")
        .Caption = "Cnt%"
        .Function = xlCount
        .NumberFormat = "0.0%"
    End With 
'The exact same thing happens with the above. The Function and NumberFormat are correct on the worksheet.
'But the Caption for this field on the sheet is left at 'Count of Net2'

'Now an even bigger issue occurs, as i try to refer to that field by the Caption I assigned, and VBA breaks with an error.
    With ActiveSheet.PivotTables(TabNam).PivotFields("Cnt%")
        .Calculation = xlPercentOfColumn
    End With

Doesn't it seem that the .Caption assignment is simply being ignored?

And if so, how could i work around this? Or is the .Caption property simply upsettable within VBA?
(Note: When recording and i change the Caption in the dialog box, what is recorded is the above code using the .Caption property to assign the new Caption.

Thank you if you have any thoughts.

 

[TomCon]

Well i solved this myself so i thought i would post it here in case anybody else needs the information.

When you go into the PivotTable field dialog, and set a new Function, a new Caption, and a Number Format, what the macro records is the following (as above).

    With ActiveSheet.PivotTables(TabNam).PivotFields("Sum of Net")
        .Caption = "Cnt"
        .Function = xlCount
        .NumberFormat = "0"
    End With

But, the .Caption MUST be set AFTER the setting of the .Function, in VBA code. So, the fix for the issue is to reorder the setting of the properties to the following:

    With ActiveSheet.PivotTables(TabNam).PivotFields("Sum of Net")
        .Function = xlCount
        .NumberFormat = "0"
        .Caption = "Cnt"
    End With

I would consider this a bug in the recording of the macro. Of course a recorded macro is not intended to be resilient, and might not produce the same result when played back if something has changed on the sheet relative to what the recorded macro does. But this is a case where absolutely nothing has changed, and yet the macro recording will not play back and produce a correct result. Microsoft should fix the recorder to produce code that can be played back to produce the same result as when it is recorded, if nothing else relative to the recording has changed on the sheet.

 

[RoryA]

Why not simply assign the caption and function you want initially here:

ActiveSheet.PivotTables(TabNam).AddDataField ActiveSheet.PivotTables( _
        TabNam).PivotFields("Net"), "Sum of Net", xlSum