Create PieChart => Error 91

[witchcraftz]

I have a code where I am pulling data from a sheet and then creating Pie charts based on the data.

My proof of concept code uses the following code and works - but is too simple since I need to create 3 charts from each line:

With ws            
            .Shapes.AddChart.Select
            ActiveChart.SetSourceData Source:=Range(.Name & "!" & "D" & iRow & ":E" & iRow)
            ActiveChart.ChartType = xlPie
            
            ActiveChart.Location Where:=xlLocationAsObject, Name:="PieCharts"
            
End With

So I have created the following code that dynamically changes the range but it keeps giving me error 91 on the "ActiveChart.SetSourceData Source:=Range" line.

With ws
                txtSource = .Name & "!" & ConvertToLetter(iCol + 16) & iRow & ":" & ConvertToLetter(iCol + 17) & iRow                
                .Shapes.AddChart.Select
                ActiveChart.SetSourceData Source:=Range(txtSource)
                ActiveChart.ChartType = xlPie
                
                ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheet3.Name '"PieCharts"


End With

Keep in mind that this is a sample and that in reality I have 500+ lines with 3 data columns on which to create pie charts.

Here is the sample workbook:

https://drive.google.com/open?id=1bFEBIvq8IwONk5jv9DeyC7rLHEnu_Nsk

 

[Worf]

This works for me:

Sub CreateChartsFinal()
Dim iRow As Integer, iCol%, ws As Worksheet, Shp As Shape, i%
Dim txtSource As String, txtChartName As String, txtChartTitle$
Set ws = Sheet4 'Sheets("Data")
ws.Activate
For iCol = 1 To 3 Step 2
    For iRow = 2 To 3
        With ws
            txtSource = ConvertToLetter(iCol + 16) & iRow & ":" & ConvertToLetter(iCol + 17) & iRow
            txtChartTitle = .Range("G" & iRow).Text & " " & .Range(ConvertToLetter(iCol + 16) & "1").Text
            txtChartName = "ChartRow" & iRow & "Col" & iCol
            .Shapes.AddChart.Select
            With ActiveChart
                .SetSourceData Sheets(ws.Name).Range(txtSource)
                .ChartType = xlPie
                .HasTitle = True
                .ChartTitle.Text = txtChartTitle
                .Parent.Cut
            End With
            Sheet3.Paste
        End With
Next iRow, iCol
Set ws = Nothing
For i = 1 To Sheet3.ChartObjects.Count      ' organize charts
    Sheet3.ChartObjects(i).Top = i * 14
    Sheet3.ChartObjects(i).Left = i * 18
Next
End Sub

 

[witchcraftz]

Excellent! I hadn't come across the idea of cut+paste into the other sheet for this before but it makes sense.