I’m using Excel 2010 and I have some VBA code that does the following things.
• Makes a copy of sheet 1 and calls it MySheet.
• Inserts a column and does several calculations in MySheet.
• Attempts to perform a pivot table from the data in MySheet and place this Pivot table on a new and separate sheet.
It is this last step with which I am having trouble. I recorded the Pivot table macro manually but upon playback it always breaks on the second line below. It is the line that begins with the words ActiveWorkbook.PivotCaches. Can someone tell me what I’m doing wrong? Thank you.

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"MySheet!R1C1:R35C4", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet27!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet27").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("CC_DD")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
Sheets("Sheet25").Select
Sheets("Sheet25").Move After:=Sheets(3)
Sheets("Sheet25").Select
Sheets("Sheet25").Name = "Pivot Table"