CODEWITHSUNDEEP
excelvbachartspivot-tableexcel-charts
Shaon
Shaon

Reputation: 153

VBA creating chart error

I am trying to create a chart from pivot table but I am getting an error. My code is given below:

Sub Chart()
'
' chart Macro
  Dim shp As Chart

'
    Set shp = Charts.Add
    Worksheets("pivot").Select
    Range("B5:E5").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("Pivot!$A$3:$E$5")
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Tools Sold"
    With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Consolidated"

    ActiveSheet.Shapes("charts").LockAspectRatio = msoTrue
    ActiveChart.ShowValueFieldButtons = False
    ActiveSheet.ChartObjects("charts").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ApplyDataLabels

    End With
End Sub

I got a run time error when I debugging line 

ActiveSheet.Shapes("charts").LockAspectRatio = msoTrue

I am a beginner. So not be able to resolve this issue. I am also attached the error screenshot and the line. How to resolve this issue? enter image description here

Upvotes: 0

Views: 2318

Answers (3)

DisplayName
DisplayName

Reputation: 13386

you don't need neither any shape object nor all that activating/selecting

With Worksheets("pivot").Shapes.AddChart.Chart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Range("Pivot!$A$3:$E$5")
    .HasTitle = True
    .ChartTitle.Characters.Text = "Consolidated"
    .SeriesCollection(1).ApplyDataLabels
    .SeriesCollection(2).ApplyDataLabels
    .SeriesCollection(3).ApplyDataLabels
    .Location Where:=xlLocationAsNewSheet, Name:="Tools Sold"
End With

Upvotes: 0

Imran Malek
Imran Malek

Reputation: 1719

Add these lines before the error message and refer to your chart with the name given with cht.Name

Dim cht As Shape
Set cht = ActiveSheet.Shapes(1)
    cht.Name = "chart001"

Upvotes: 2

ashleedawg
ashleedawg

Reputation: 21619

I would suggest that when you're trying to automate any of Excel's built-in tasks, instead of trying to figure out the exact VBA yourself, you use the Macro Recorder to record your actions as you do the steps manually (in this case, create a chart from a PivotTable), and then you and view and edit the generated code as required.

Edit:

If you're not sure what your chart is named, one way to find out is by running this:

Sub ListCharts()
    Dim x
    For Each x In ActiveSheet.ChartObjects
      Debug.Print x.Name
    Next x
End Sub

Use Ctrl+G to open the Immediate Window to view the results (if it's not already open).

More Information:

Upvotes: 1

Related Questions