Excel VBA/Macro Error

[theworm1810]

Hello,

I have a project for my class that requires me to make a pivot table for a given set of stock data and a pivot graph. However, whenever I make my pivot graph and add a title and data labels it does not work and I get an error. Could someone please help fix my code?


Sub Macro6()
'
' Macro6 Macro
'

'
Range("F35:H51").Select
ActiveSheet.Shapes.AddChart2(251, xlPie).Select
ActiveChart.SetSourceData Source:=Range("Recommendation!$F$35:$H$51")
ActiveSheet.Shapes("Chart 3").IncrementLeft 288.1719685039
ActiveSheet.Shapes("Chart 3").IncrementTop 46.2365354331
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Test"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Test"
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).ApplyDataLabels
ActiveChart.FullSeriesCollection(1).DataLabels.Select
Selection.ShowValue = False
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).ApplyDataLabels
ActiveChart.FullSeriesCollection(1).DataLabels.Select
Selection.ShowPercentage = True
Selection.ShowValue = False
Selection.Position = xlLabelPositionOutsideEnd
Selection.Separator = ", "
End Sub






Thank you

 

[mart37]

When you for the second time run this macro the add line creates a chart with a different name. Example: CHART 4
So your line is giving an error.
You need to use ACTIVECHART or use an object like this:

Dim cht As Object
'Your data range for the chart
  Set rng = "Range("Recommendation!$F$35:$H$51")
'Create a chart
  Set cht = ActiveSheet.Shapes.AddChart2
'Give chart some data
  cht.Chart.SetSourceData Source:=rng
'Determine the chart type
  cht.Chart.ChartType = xlPie
'Position
[B] [/B] cht.Chart.IncrementLeft 288.1719685039