PDA

View Full Version : [SOLVED] Issue recording Pivot tables



jmutsche
12-13-2013, 01:58 PM
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
12-14-2013, 10:31 AM
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
12-16-2013, 04:43 AM
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:
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

jmutsche
12-16-2013, 06:57 AM
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.