Embedding charts in a sheet - VBA

[yomomo]

So i've been trying to make a sub that makes charts off of some pivot tables in the sheet, the sheet name is "statistics". I was able to make it work a couple of times but now it doesn't work and when i debug it keeps telling me that

ch2016.Chart.SetSourceData ActiveSheet.Range("A19").CurrentRegion

is the issue and haven't changed anything? i really don't get it.

Sub Opgave4()

Dim ch2015 As Shape
Dim ch2016 As ChartObject
Dim ch2017 As ChartObject


ActiveSheet.Select


Set ch2015 = ActiveSheet.Shapes.AddChart(Left:=0, Top:=0, Width:=450, Height:=200)
Set ch2016 = ActiveSheet.ChartObjects.Add(Left:=450, Top:=0, Width:=450, Height:=200)
Set ch2017 = ActiveSheet.ChartObjects.Add(Left:=900, Top:=0, Width:=450, Height:=200)


ch2015.Chart.SetSourceData ActiveSheet.Range("A2").CurrentRegion
ch2016.Chart.SetSourceData ActiveSheet.Range("A19").CurrentRegion
ch2017.Chart.SetSourceData ActiveSheet.Range("G1").CurrentRegion




End Sub

 

[Worf]

The code below works for me; what error message do you get? Is it “the method set source data of object chart has failed”?
Check if the information in the message box makes sense or not.

Sub Opgave4()
Dim ch2015 As Shape, ch2016 As ChartObject, ch2017 As ChartObject, _
s As Worksheet, a(1 To 3) As Range
Set s = ActiveSheet
Set a(1) = s.[a2].CurrentRegion
Set a(2) = s.[a19].CurrentRegion
Set a(3) = s.[g1].CurrentRegion
MsgBox "Ranges are: " & vbLf & a(1).Address & vbLf & a(2).Address _
& vbLf & a(3).Address
Set ch2015 = s.Shapes.AddChart(Left:=0, Top:=0, Width:=450, Height:=200)
Set ch2016 = s.ChartObjects.Add(Left:=450, Top:=0, Width:=450, Height:=200)
Set ch2017 = s.ChartObjects.Add(Left:=900, Top:=0, Width:=450, Height:=200)
ch2015.Chart.SetSourceData a(1)
ch2016.Chart.SetSourceData a(2)
ch2017.Chart.SetSourceData a(3)
End Sub

 

[Worf]

The code below works for me; what error message do you get? Is it “the method set source data of object chart has failed”?
Check if the information in the message box makes sense or not.

Sub Opgave4()
Dim ch2015 As Shape, ch2016 As ChartObject, ch2017 As ChartObject, _
s As Worksheet, a(1 To 3) As Range
Set s = ActiveSheet
Set a(1) = s.[a2].CurrentRegion
Set a(2) = s.[a19].CurrentRegion
Set a(3) = s.[g1].CurrentRegion
MsgBox "Ranges are: " & vbLf & a(1).Address & vbLf & a(2).Address _
& vbLf & a(3).Address
Set ch2015 = s.Shapes.AddChart(Left:=0, Top:=0, Width:=450, Height:=200)
Set ch2016 = s.ChartObjects.Add(Left:=450, Top:=0, Width:=450, Height:=200)
Set ch2017 = s.ChartObjects.Add(Left:=900, Top:=0, Width:=450, Height:=200)
ch2015.Chart.SetSourceData a(1)
ch2016.Chart.SetSourceData a(2)
ch2017.Chart.SetSourceData a(3)
End Sub

 

[yomomo]

Exactly, it's the “the method set source data of object chart has failed” i keep getting and i don't know what to do, i tried to change "Activesheet" to "Worksheets("Statistics"), which is the sheet the data is in, but still to no avail. Though it sometimes work when i double-click on the name of the sheet as if i'm renaming the sheet and then start the sub.

 

[Worf]

Try the version below which explicitly names the sheet and see if the ranges informed on the message box make sense.
If you get an error, chart that range manually to check if it works.
As a last resort, post a link to your workbook with sensitive data changed.

Sub Opgave4()
Dim ch2015 As Shape, ch2016 As ChartObject, ch2017 As ChartObject, _
s As Worksheet, a(1 To 3) As Range
Set s = Sheets("statistics")
Set a(1) = s.[a2].CurrentRegion
Set a(2) = s.[a19].CurrentRegion
Set a(3) = s.[g1].CurrentRegion
MsgBox "Ranges are: " & vbLf & a(1).Address & vbLf & a(2).Address _
& vbLf & a(3).Address
Set ch2015 = s.Shapes.AddChart(Left:=0, Top:=0, Width:=450, Height:=200)
Set ch2016 = s.ChartObjects.Add(Left:=450, Top:=0, Width:=450, Height:=200)
Set ch2017 = s.ChartObjects.Add(Left:=900, Top:=0, Width:=450, Height:=200)
ch2015.Chart.SetSourceData a(1)
ch2016.Chart.SetSourceData a(2)
ch2017.Chart.SetSourceData a(3)
End Sub

 

[yomomo]

It didn't work, it still gives me “the method set source data of object chart has failed”.

 

[Worf]

o The message box tells you what the offending range is.Try charting it manually. Is it a pivot table?
o If you prefer, post a link to your workbook for a quicker solution.

 

[Jon Peltier]

Do you get the error for all of the charts, or just that one?