Recorded Pivot Table Macro Doesn't Do What I Recorded!

**diakonos1984** · 03-26-2013, 08:55 AM

I'm just getting in to pivot tables, so in my attempt to control them with VBA I recorded my self making a pivot table to count the occurrences of an item. I based what I was trying to do off of this youtube video: http://www.youtube.com/watch?v=OlTsegp3cDY

In short, I have a table like this:

Items	Months
Bananas	May
Apples	June
Banans	July
Bananas	August
Apples	September
Oranges	November

It's on Sheet49, starting in R1C1. I recorded the following Macro while creating a PivotTable that includes the number of occurrences of each item. When I'm done, it looks like this:
Pivot1.PNG

However, when I delete the pivot table, and run the recorded Macro (see below):

Sub Pivot4()
'
' Pivot4 Macro
'

'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet49!R1C1:R7C1", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet49!R1C4", TableName:="PivotTable6", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet49").Select
    Cells(1, 4).Select
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Items")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Items"), "Count of Items", xlCount
End Sub

All I get is this:
Pivot2.PNG

I loose all the data in the PivotTable and only get the total number of items!

What is going on?

Thanks for any help!

**JosephP** · 03-26-2013, 10:02 AM

change the order

Sub Pivot4()
'
' Pivot4 Macro
'

'
    Dim pt As PivotTable
    Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet49!R1C1:R7C1", Version:=xlPivotTableVersion10).CreatePivotTable( _
        TableDestination:="Sheet49!R1C4", TableName:="PivotTable6", DefaultVersion _
        :=xlPivotTableVersion10)
    With pt
        .AddDataField .PivotFields("Items"), "Count of Items", xlCount
        With .PivotFields("Items")
            .Orientation = xlRowField
            .Position = 1
        End With
    End With
End Sub

**diakonos1984** · 03-26-2013, 10:56 AM

Unbelievable. You're a genius man. It's like you actually know what you are doing! That fixed it. Thanks a lot.

...still wondering why the order is wrong if I simply recorded the macro...

Like your signature, BTW, JosephP.

**JosephP** · 03-26-2013, 11:01 AM

the macro recorder is a pretty old and ornery critter and its memory ain't as good as it used to be so sometimes it gets things wrong. especially in 2007 where it often forgets to record anything at all ;-)