Hello,

I'm trying to create a pivot table in a new workbook. The only data is one column, 38 rows long, listing months (coppied from another wb). I'm trying to create a pivot table that counts the number of times each month appears:

This is the code I've got (lifted from a recorded macro). When I was recording the macro, it worked. But running the code creates a pivot table with one row that counts the number of entries in the range, not making any differentiation between values.
I'm new to using Pivot Tables in VBA. Any ideas?

Dim rngData As Range

'Set range and turn into list
Set rngData = nWb.Worksheets("OpenedPerMonth").Range("A5:A38")

'has header Month Opend in A5
nWb.Worksheets("OpenedPerMonth").ListObjects.Add(xlSrcRange, Range("A5:A38" ), , xlYes).Name = _
       "OpenedMonth"


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        rngData).CreatePivotTable TableDestination:= _
        "OpenedPerMonth!R5C3", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month Opened")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Month Opened"), "Count of Month Opened", xlCount