So, I just created a pivot chart from certain information, but now I need to be able to change the chart style (I want to make it the xlBarStacked style)
This is my original code, and it works perfect,
Sub Macro7()
Dim pc As PivotCache, pt As PivotTable
Dim cht As Chart
Set pc = ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, SourceData:= _
"ICS-GSD-Account Management!R2C1:R1106C8", Version:=xlPivotTableVersion10)
Set pt = pc.CreatePivotTable(TableDestination:=Sheets("ICS-GSD-Account Management").Cells(2, 9), _
TableName:="", DefaultVersion:=xlPivotTableVersion10)
Sheets("ICS-GSD-Account Management").Select
Cells(2, 9).Select
Set cht = ActiveSheet.Shapes.AddChart.Chart
With cht
.SetSourceData Source:=Sheets("ICS-GSD-Account Management").Range("$I$2:$O$15")
.ChartType = xlColumnClustered
End With
With ActiveSheet.PivotTables("").PivotFields("Date")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("").PivotFields("Time")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
PivotFields("Handled"), "Sum of Handled", xlSum
ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
PivotFields("Aband Within"), "Sum of Aband Within", xlSum
With ActiveSheet.PivotTables("").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
PivotFields("Aband Diff"), "Sum of Aband Diff", xlSum
ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
PivotFields("Dequeued"), "Sum of Dequeued", xlSum
End Sub
I run into problems when I try to change the chart style, because each time I run the macro, the number of the chart changes and I'm not sure how to set the chart to a certain name, or how to keep the variables floating for the following code, which changes the chart style and one of the colors of the bars:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartType = xlBarStacked
ActiveChart.Legend.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(4).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWorkbook.ShowPivotChartActiveFields = False
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Select
any suggestions?
Bookmarks