Pivot Table in VBA

[Mikeykt]

Hi

I'm having problems running my vba script for creating a new pivot table, can anyone tell me where i've gone wrong?

Thanks




Sub RSPivotNum()
'
' RSPivotNum Macro
'
'
Sheets("RSPIVOT").Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("Inactive RG by Recruit Type 2").Select
Columns("A:G").Select


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Inactive RG by Recruit Type 2!A:G").CreatePivotTable TableDestination:="RSPIVOT!R1C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12



Sheets("RSPIVOT").Select
Cells(1, 1).Select

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Behaviour")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Recency")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Segment")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Recruitment Summary")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Recruitment Source")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("No Supporters"), "Count of No Supporters", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of No Supporters")
.Caption = "Sum of No Supporters"
.Function = xlSum
End With

ActiveSheet.PivotTables("PivotTable1").Name = "RSPN"

End Sub

 

[Mikeykt]

v2007

 

[RoryA]

Try changing this line:

Set PT = pc.CreatePivotTable(TableDestination:=wksPivot.Cells(3, 1), _
                                   TableName:="", DefaultVersion:=xlPivotTableVersion12)

to just this:

Set PT = pc.CreatePivotTable(TableDestination:=wksPivot.Cells(3, 1), TableName:="")

 

[Mikeykt]

Perfect.

That has worked.

Thanks so much for your help.

 

[Mikeykt]

Hi Rory,

Sorry to bother you again.

I've tried creating a Command Button on a seperate worksheet that links to this Macro, and now it keeps failing at the create pivot stage again:

Set PT = pc.CreatePivotTable(TableDestination:=wksPivot.Cells(3, 1), TableName:="")

Do you have an idea why?

Thanks

 

[RoryA]

With what error?

 

[Mikeykt]

Sorry runtime error 1004

 

[RoryA]

Don't know offhand, and I will be away for the next week or so, I'm afraid.