VBA Code to replace the existing pivot chart and add new one in the same sheet.

[Aanup]

Hi Chandoo/All,

Firstly its one of the awesome sites to explore the excel. Thanks a lot for creating it.

I have recorded the macros which will create the pivot charts and pivot table in the same sheet. Its good if I rn it for first time, the problem occurs when i Run it next time , it display an error. This is because as the same sheet is already having the Pivot chart and tables. Is der any way wherein I can replace the existing charts with new pivot chart in the same sheet or Code to delete the existing the sheet whcih contains the Pivot charts and create the new sheet and add the pivot charts and pivot tables.

Appreciate all your help here.

Regards,
Aanup

Below is the code ..plz help ..

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Atos Open!R1C1:R229C18", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet5!R12C6", TableName:="PivotTable4", _
DefaultVersion:=xlPivotTableVersion14

MsgBox ("Trend all created")

Sheets("Sheet5").Select
Cells(12, 6).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("'Sheet5'!$F$12:$H$29")
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Title")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Priority"), "Count of Priority", xlCount
ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Priority"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Title"), "Count of Title", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Priority")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Priority Trend,Raised til now."
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Priority Trend,Raised til now."
With Selection.Format.TextFrame2.TextRange.Characters(1, 30).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 30).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
'ActiveSheet.Shapes("Chart 3").IncrementLeft 78
'ActiveSheet.Shapes("Chart 3").IncrementTop -81.75
ActiveSheet.PivotTables("PivotTable4").CompactLayoutRowHeader = "Priority"
Range("F19").Select
ActiveSheet.PivotTables("PivotTable4").PivotSelect "Priority[All]", _
xlLabelOnly + xlFirstRow, True
Range("F12").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
'ActiveSheet.ChartObjects("Chart 4").Activate
'ActiveSheet.Shapes("Chart 4").IncrementLeft -169.5
'ActiveSheet.Shapes("Chart 4").IncrementTop 51