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

 

[RoryA]

If you get an error, what is the error and on which line does it occur?

 

[Mikeykt]

Hi

Sorry i forgot to highlight that

it occurs at this stage:

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

 

[RoryA]

Your source data sheet name has spaces in it, so needs to be enclosed in single quotes:

SourceData:= _
"'Inactive RG by Recruit Type 2'!A:G"

It's also not a good idea to use entire columns as the source for a pivot table generally.

 

[Mikeykt]

Hi

Thanks for the tip:

It's still coming back with a runtime error 5,

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

Can you see why at all, is there something I've missed?

Thanks

 

[RoryA]

Is there already a pivot table on the RSPIVOT sheet?

 

[Mikeykt]

No, as the code above this code which the creates the Pivot deletes the old one, this is meant to replace it.

Ah but they both are named "PivotTable1", is this the problem?

Is this a problem?

As I need them both to have the same name, so I can rename it later on in my code, if it's left as a veriable pivot name then this won't work.

Any advice?

Thanks

 

[RoryA]

Can you test this version:

Sub RSPivotNum()
'
' RSPivotNum Macro
'
'
   Dim wksPivot          As Worksheet
   Dim wksData           As Worksheet
   Dim pc                As PivotCache
   Dim PT                As PivotTable

   Set wksPivot = Sheets("RSPIVOT")
   Set wksData = Sheets("Inactive RG by Recruit Type 2")

   wksPivot.UsedRange.Clear

   With wksData
      Set pc = .Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                                       "'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(0, 0, xlR1C1))
      Set PT = pc.CreatePivotTable(TableDestination:=wksPivot.Cells(3, 1), _
                                   TableName:="", DefaultVersion:=xlPivotTableVersion12)

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

      .Name = "RSPN"
   End With

End Sub

 

[Mikeykt]

Hi

Sorry, again there is a runtime error 5 on the create section:



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

 

[RoryA]

Which version of Excel are you using?