Issue recording Pivot tables

[jmutsche]

Columns("A:H").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Summary Collected Pivot!R1C1:R1048576C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Summary Collected Pivot!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion12
'
    
    Sheets("Summary Collected Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Row Labels")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("201308"), "Count of 201308", xlSum
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("201309"), "Count of 201309", xlSum
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("201310"), "Count of 201310", xlSum
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("201311"), "Count of 201311", xlSum

Having trouble making a pivot table its spitting out error 5 invalid procedure or argument. the weird thing is I recorded this macro, and I use to make pivot tables all the time like this and now its not working? any ideas

[SamT]

Where is the error occurring?

That is not all of the code. The error condition may be being enabled, caused, or triggered by some other part.

[Aflatoon]

It seems you are trying to use the same sheet and area for the source data and the pivot table - probably not a good idea.

I suggest you break the steps up and use some variables as it will make debugging easier:
[vba] Dim pc As Excel.PivotCache
Dim pt As Excel.PivotTable
Dim ws As Worksheet

Set ws = Worksheets("Summary Collected Pivot")

Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'" & ws.Name & "'!" & ws.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), _
Version:=xlPivotTableVersion12)

Set pt = pc.CreatePivotTable(TableDestination:="", DefaultVersion:=xlPivotTableVersion12)
'
With pt
With .PivotFields("Row Labels")
.Orientation = xlRowField
.Position = 1
End With
.AddDataField .PivotFields("201308"), "Count of 201308", xlSum
.AddDataField .PivotFields("201309"), "Count of 201309", xlSum
.AddDataField .PivotFields("201310"), "Count of 201310", xlSum
.AddDataField .PivotFields("201311"), "Count of 201311", xlSum
End With

[/vba]

[jmutsche]

Thanks, I was able to get it from here. I just thought it was a little strange that I use to be able to record that process. it just made it a little faster.