Record macro does not work for identical pivot fields

[GoJakie]

Hello

I made a pivot to show Issue in Row Labels on the left and Count of Issue on the right. The same thing if I do it in macro, it does not create the pivot as both the fields Issues are identical. If I change the field Count of Issue to City (highlighted in bold in the macro given below) then everything works fine.

I wish to have the SAME field Issue and Count of Issue. What should I do. Appreciate some help here. Thanks

Here is the code

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RAW!R1C1:R3289C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet16!R3C1", TableName:="PivotTable5", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet16").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Issue")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("[B]Issue[/B]"), "[B]Count of Issue[/B]", xlCount

 

[jorismoerings]

Hi,

try changing

ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("[B]Issue[/B]"), "[B]Count of Issue[/B]", xlCount

Into

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Issue")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlcount
End With

 

[GoJakie]

gr8.. that worked !
Thanks a lot