Error message with VBA and pivot chart

[Speedy]

Could anyone help me with this problem. I am trying to create a pivot table and then a pivot chart based on the data in the pivot table. Sometimes the code works and sometimes I get the error message described below. Could anyone take a look and help me out with this.

See code below.

Error occurs with line setting the source data for the chart which is:

cht2.SetSourceData Source:=chrtdata

The error message given by excel is:

Automation error (Error 440)
Method 'SetSourceData' of object '_Chart' failed


Sub MakePivotTablesStatewide()

Application.ScreenUpdating = False

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Dim z As Long, i As Long, j As Long, z2 As Long
Dim DataName() As String
Dim PivItem As PivotItem
Dim chrtdata As Range
Dim cht As ChartObject
Dim cht2 As Chart


Set WSD = Worksheets("Statewide")

For Each PT In WSD.PivotTables 'Delete any prior pivot tables Statewide
PT.TableRange2.Clear
Next PT

For Each cht In WSD.ChartObjects 'Delete charts statewide
cht.Delete
Next cht

FinalRow = Worksheets("CombinedData").Cells(Rows.Count, 1).End(xlUp).Row 'Define data range for pivot table
FinalCol = Worksheets("CombinedData").Range("K1").Column
i = FinalCol - 5 '# of columns to get excluding CZ, Technology, Sector, Year, and Utility

ReDim DataName(1 To i)

For j = 1 To i 'store names of columns to be aggregated via pivot table
DataName(j) = Worksheets("CombinedData").Range("A1").Offset(0, 3 + j)
Next j

Set PRange = Worksheets("CombinedData").Cells(1, 1).Resize(FinalRow, FinalCol) 'Set common data range and cache
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

z = 10

'STATEWIDE
'build pivot table for statewide (filter by CZ and Sector)
For j = 1 To i
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(z, 1)) 'Create pivot table
PT.ManualUpdate = True 'Turn off auto updating while building table
PT.AddFields RowFields:="Year", ColumnFields:="Technology", PageFields:=Array("Sector", "Utility") 'Set up row and column fields
With PT.PivotFields(DataName(j)) 'Set up the data field
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
PT.PivotFields("Sum of " & DataName(j)).NumberFormat = "#,##0"
PT.NullString = "0"
PT.ManualUpdate = False
PT.ManualUpdate = True
z2 = WSD.Cells(z, 5 + PT.TableRange2.Columns.Count).Column 'start building chart
Set chrtdata = PT.TableRange1.Offset(1, 0).Resize(PT.TableRange1.Rows.Count - 2)
WSD.Shapes.AddChart(xlAreaStacked, _
Left:=WSD.Cells(z, z2).Left, Top:=WSD.Cells(z, z2).Top, _
Width:=1000, Height:=500).Select
Set cht2 = ActiveChart
cht2.SetSourceData Source:=chrtdata
cht2.SetElement (msoElementChartTitleAboveChart)
cht2.ChartTitle.Text = DataName(j)
z = z + WSD.Cells(10 + PT.TableRange2.Rows.Count, 1).Row
Next j
'build pivot table for statewide (filter by CZ and Technology)
For j = 1 To i
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(z, 1)) 'Create pivot table
PT.ManualUpdate = True 'Turn off auto updating while building table
PT.AddFields RowFields:="Year", ColumnFields:="Sector", PageFields:=Array("Technology", "Utility") 'Set up row and column fields
With PT.PivotFields(DataName(j)) 'Set up the data field
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
PT.PivotFields("Sum of " & DataName(j)).NumberFormat = "#,##0"
PT.NullString = "0"
PT.ManualUpdate = False
PT.ManualUpdate = True
z2 = WSD.Cells(z, 5 + PT.TableRange2.Columns.Count).Column 'start building charts
Set chrtdata = PT.TableRange1.Offset(1, 0).Resize(PT.TableRange1.Rows.Count - 2)
WSD.Shapes.AddChart(xlAreaStacked, _
Left:=WSD.Cells(z, z2).Left, Top:=WSD.Cells(z, z2).Top, _
Width:=1000, Height:=500).Select
Set cht2 = ActiveChart
cht2.SetSourceData Source:=chrtdata
cht2.SetElement (msoElementChartTitleAboveChart)
cht2.ChartTitle.Text = DataName(j)
z = z + WSD.Cells(10 + PT.TableRange2.Rows.Count, 1).Row
Next j

Application.ScreenUpdating = True

End Sub

 

[Speedy]

bump....

 

[Speedy]