You don't specify the chart's source data in the code, e.g.:

Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").PivotTables(1).TableRange 1

Or, see Jon Peltier's code samples for creating charts:

http://www.peltiertech.com/Excel/Cha...kChartVBA.html

Lee Hunter wrote:
I recorded a macro creating and formatting a pivot table.
Created a command Button with an On_Click event
When the code runs, it fails at the indicated point below.
MsgBox shows that there is no seriescollection, even though, obviously,
there was when the macro was recorded. Unchanged data source.

Must have something to do with the ApplyCustomType.

Any Ideas?
Thanks for the help!

Code Below

Private Sub CommandButton1_Click()
'
' Chart Macro
' Macro recorded 5/19/2005 by Lee Hunter
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim MyWkBk As String, MyWkSht As String, Tabledef As String
MyWkBk = ActiveWorkbook.Name
MyWkSht = ActiveWorkbook.Worksheets(1).Name
Tabledef = "[" & MyWkBk & "]" & MyWkSht & "!R3C3"
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;Defa ultDir=S:\1310\Quality\Metrics;DriverId=25;FIL=MS Access;MaxBuf" _
), Array("ferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT ArrChtInp.Trip, ArrChtInp.Stat, ArrChtInp.`Num of Occ`" &
Chr(13) & "" & Chr(10) & "FROM `S:\1310\Quality\Metrics\Trip`.ArrChtInp
ArrChtInp" & Chr(13) & "" & Chr(10) & "ORDER BY ArrChtInp.Trip" _
)
.CreatePivotTable TableDestination:=Tabledef, TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Trip", _
ColumnFields:="Stat"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Num of
Occ").Orientation = _
xlDataField
Charts.Add
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Columns with Depth"
MsgBox ActiveChart.SeriesCollection.Count ***** Now shows 0 ******
************ ActiveChart.SeriesCollection(2).Select ****** Fails here
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html