Clash Royale CLAN TAG#URR8PPP

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?

ShapeObject

ChartObjects

ChartObjects("Chart 1")

LockAspectRatio

3 Answers
3

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"


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.

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).

ShapeRange.LockAspectRatio

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.