Pivot Table Count Macro

Question

I´m trying to do a Macro that includes a Pivot Table, but I´m unable to make it work... When I record the macro all works OK, but when I execute it again, it does not deliver the expected results...

This is an Example of Original Data

This is what I need

I need the macro to have multiple Colums by Level, and Count each Level But I´m unable to reproduce that with a macro...

Here is the code I tried:

NomHoja = ActiveSheet.Name
    Range("A1:B11").Select
    Sheets.Add
NomHojaTD = ActiveSheet.Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'" & NomHoja & "'!R1C1:R11C2", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="'" & NomHojaTD & "'!R3C1", TableName:="Tabla dinámica3", _
        DefaultVersion:=xlPivotTableVersion15
    Sheets(NomHojaTD).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Type ")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Level")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabla dinámica3").AddDataField ActiveSheet.PivotTables _
        ("Tabla dinámica3").PivotFields("Level"), "Cuenta de Level", _
        xlCount

But this is what I get instead... This is what I get...

Seems I cannot use "Level" as Row and Ccount at the same time...

Answer 1

This works for me - it seems to matter what order you add the fields in...

Sub Tester()
    Dim wb As Workbook, pc As PivotCache, pt As PivotTable
    Dim wsData As Worksheet, wsPT As Worksheet, rngData As Range
    
    Set wsData = ActiveSheet
    Set rngData = wsData.Range("A1").CurrentRegion 'the data for the PT
    Set wb = wsData.Parent     'parent workbook
    
    Set wsPT = wb.Worksheets.Add(after:=wsData)
    
    'create the pivot cache
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:=rngData, Version:=xlPivotTableVersion15)
    'create the pivot table
    Set pt = pc.CreatePivotTable(TableDestination:=wsPT.Range("A3"))
    
    With pt.PivotFields("Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    pt.AddDataField pt.PivotFields("Level"), _
            "Cuenta de Level", xlCount
    
    With pt.PivotFields("Level")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
End Sub