excel chart from vba

[chainedtodesk]

i have a process that creates an excel chart, i would like for the chart to not have the filter pivot table options. these are gray buttons to the top/bottoms/sides based on the pivot table.

CODE:

Sub chart_share1()
Start_Row = 13
Chart_Name = "NEW PRODUCTS BOOKINGS"
Counter = 1
Point_Tot = 4 ' # of Fiscal Years
'Find Series Tot
Do Until Cells(Start_Row, Counter).Value = "" Or Counter = 50
Counter = Counter + 1
Loop
Series_Tot = Counter - 2
'Create Chart
Sheets("Sheet1").Select
Range(Cells(Start_Row, 2), Cells(Start_Row + Point_Tot - 1, 1 + Series_Tot)).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(Cells(Start_Row, 2), Cells(Start_Row + Point_Tot - 1, 1 + Series_Tot))
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetElement (msoElementChartTitleAboveChart) 'Add Title
ActiveChart.ChartTitle.Text = Chart_Name
'Move Chart
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
ActiveChart.Parent.Name = Chart_Name
With ActiveSheet.ChartObjects(Chart_Name)
.Left = 375 ' CHART LOCATION (HORIZONTAL)
.Top = 232 ' CHART LOCATION (VERTICAL)
End With
'Series_Counter = 1
'Do Until Series_Counter = Series_Tot
' ActiveChart.SeriesCollection(Series_Counter).ApplyDataLabels
'' Point_Counter = 1
'' Do Until Point_Counter = Point_Tot - 1
'' ActiveChart.SeriesCollection(Series_Counter).Points(Point_Counter).DataLabel.Text = Sheets("Sheet3").Cells(Point_Counter, Series_Counter)
'' Point_Counter = Point_Counter + 1
'' Loop
' Series_Counter = Series_Counter + 1
'Loop
'Return to sheet1
Sheets("Sheet1").Select
Cells(1, 1).Select
End Sub

 

[SkipVought]

hi,

Then make the Chart Data without a PivotTable, perhaps using MS Query or even Summary Formulas based on your criteria (SUMPRODUCT())

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[chainedtodesk]

okay thanks, i will try it with the summary formulas.

 

[SkipVought]

You might consider sumarizing the entire table on the ROW DATA that you would have used in the Pivot.

Than all you have to do is filter the summary table on the ROW DATA that is appropriate. There is an option in your chart to only plot visible cells. I believe that's the default, at least it has been for me. You can filter directly in the AutoFilters OR you can be fancy and provide some ComboBoxes to make the selection(s)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

If the pivot chart is what you need except of pivot filters visible, you can hide them. With code - it's HasPivotFields = True/False property of Chart object.

combo