I am trying to create a macro that will automatically create a pivot table and chart for the user. I get the runtime error and excel will bring up the MS VBA editor with this section of code highlighted

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"NEW_ORDERS!R2C20:R103C21", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

I would suspect this has something to do with me using Excel 2007 and saving the file as a xls instead of an xlsm. However I have tried using both file formats.

Any help you can provide would be greatly appreciated. (Below is the entire section of code)

Thanks for the help



Sub NEWORDER_DEMAND()
'
' NEWORDER_DEMAND Macro
' CREATES PIVOT CHART SHOWING THE DAILY DEMAND REQUIRED BY ALL OF THE NEW ORDERS JUST ENTERED
'

'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"NEW_ORDERS!R2C20:R103C21", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$G$14")
ActiveChart.ChartType = xlColumnClustered
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ORDER DUE DATE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("EST TOTAL TIME TO PROCESS JOB hh:mm:ss"), _
"Count of EST TOTAL TIME TO PROCESS JOB hh:mm:ss", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of EST TOTAL TIME TO PROCESS JOB hh:mm:ss")
.Caption = "Sum of EST TOTAL TIME TO PROCESS JOB hh:mm:ss"
.Function = xlSum
.NumberFormat = "[h]:mm:ss"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ORDER DUE DATE")
.PivotItems("").Visible = False
.PivotItems("12:00:00 AM").Visible = False
End With
Range("A23").Select
ActiveCell.FormulaR1C1 = "TOTAL DEMAND OF NEW ORDERS AS OF"
Range("A24").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A24").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Range("A23:A24").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("D28").Select
End Sub