Creating a Pivot Table Macro in VBA

[Mikeykt]

Hi,

I'm trying to set up a macro to create a new Pivot table on a fixed sheet. So that it deletes the previous pivot table, and creates a new one on the same worksheet (as a kind of refresh).

I haven't done this for ages, so need a little guidence on why my VBA isn't working at the creating the pivot table stage.

I thought I had the code correct, but apprently not. Can anyone let me know where I went wrong?

Thanks
Mike



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.Worksheets("RSPIVOT").PivotTables("RSPN").PivotCache. _
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

 

[texasalynn]

you will need to save the file after you delete the pivot table, else Excel still thinks that pivot table is existing