Sub CreateChartsFromPivotTables()

Dim sh As Shape
Dim ws As Worksheet
Dim ch As Chart
Dim ptPicker As PivotTable
Dim ptSupDept As PivotTable
Dim ptTypeOfUse As PivotTable
Dim rngData As Range

Set ws = Worksheets("DashBoard")
Set ptPicker = ws.PivotTables("PivotPicker")
Set ptSupDept = ws.PivotTables("PivotSupDept")
Set ptTypeOfUse = ws.PivotTables("PivotTypeOfUse")

Set rngData = ptSupDept.TableRange1

Set sh = ws.Shapes.AddChart2( _
    XlChartType:=XlChartType.xlPie, _
    Left:=ws.Cells(7, 2).Left, _
    Top:=ws.Cells(7, 2).Top, _
    Width:=250, _
    Height:=200)

Set ch = sh.Chart

With ch
    .SetSourceData Source:=rngData
    .SeriesCollection(1).ApplyDataLabels Type:=xlShowPercent, AutoText:=True, LegendKey:=False, HasLeaderLines:=False
    .SeriesCollection(1).DataLabels.NumberFormat = "0.00%"
End With

Debug.Print rngData.Address

End Sub

I’m encountering an error when using the .SetSourceData method in Excel VBA to set the source data for a chart. Here’s what I’m trying to do and the problem I’m facing:

1. Objective: I want to create charts dynamically in Excel VBA based on the data from PivotTables.
2. Issue: When I attempt to set the source data for the chart using .SetSourceData, the code throws an error, and the chart doesn’t display the expected data.
3. Context: I have three PivotTables named “PivotPicker”, “PivotSupDept”, and “PivotTypeOfUse” in my “DashBoard” worksheet. Each PivotTable contains different sets of data.
4. Question: What could be causing this error with .SetSourceData?