Creating Pivot Table and Pivot Chart in a new sheet (Code no longer works)

[Dremzy]

Hi guys,

I had created a piece of code which created a pivot table from a range of data into a new sheet.

The code then create a pie chart from this data.

For some reason I think I messed with the pivot table elementof the code and it no longer works. I keep receiving an error referring to:

"run time 1004" With ActiveSheet.PivotTables("PivotTableName").PivotFields("Category")

Can you guys please help me to fix it??

Here is the full code

Sub CreatePivotTableandchart()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As Pivottable
Dim StartPvt As String
Dim SrcData As String
'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:F200").Address(ReferenceStyle:=xlR1C1)
'Create a new worksheet
Set sht = Sheets.Add
'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
With ActiveSheet.PivotTables("PivotTableName").PivotFields("Category")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Category "), "Count of Category ", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category ")
.Orientation = xlRowField
.Position = 1
End With
Set shtPTable = ActiveSheet
Range("A4:B11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie

With ActiveSheet.PivotTables("PivotTable1")

ActiveChart.ShowAllFieldButtons = False
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.ShowPercentage = True
Selection.ShowCategoryName = False
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Category of query received into mailbox:"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Category of query received into mailbox:"
Selection.Position = xlLabelPositionOutsideEnd
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 15).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 62).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "Arial"
.NameFarEast = "Arial"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 32, 96)
.Fill.Transparency = 0
.Fill.Solid
.Size = 15
.Italic = msoFalse
.Kerning = 12
.Name = "Arial"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 36, 105)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(158, 162, 162)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(3).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(205, 0, 88)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(5).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 169, 206)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(6).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(240, 179, 35)
.Transparency = 0
.Solid
End With
With ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Position = xlLabelPositionOutsideEnd
End With
Range("A1").Select
End Sub

 

[mole999]

WHERE DOES IT FALL OVER

this code
TableName:="PivotTable1")
With ActiveSheet.PivotTables("PivotTableName").PivotFields("Category")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Category "), "Count of Category ", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category ")
.Orientation = xlRowField
.Position = 1
End With
Set shtPTable = ActiveSheet
Range("A4:B11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie

With ActiveSheet.PivotTables("PivotTable1")
looks like you name the table one way and refer to it as another