You're welcome. I don't know why it doesn't include the source in the
recorded code. However, if the active cell is in the pivot table range,
the code should run correctly, without the source range being specified.

When you tried to run your recorded code, you must have had a different
cell selected, and that's when you saw the error.

Lee Hunter wrote:
Thanks for the prompt reply, Debra. It's really appreciated.

I'll add that bit to my code.

Why do you suppose that code did not show up when I recorded the macro?
Lee

"Debra Dalgleish" wrote:


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;D efaultDir=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





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