Type mismatch error when creating pivot table in Excel using VBA

Question

I am trying to combine a macro that will make a simple pivot table using data from an active worksheet. When I try to run it, I get a type mismatch error. When I run the debugger, the first section is highlighted: ActiveWorkbook.PivotCaches via xlPivotTableVersion10. Initially, the TableDestination was empty, and I thought it might be a problem, but after adding the destination I still get the same error.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ActiveSheet.UsedRange).CreatePivotTable TableDestination:="Sheet1!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) With ActiveSheet.PivotTables("PivotTable1").PivotFields("Program Name") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Dollars Awarded"), "Sum of Dollars Awarded", xlSum With ActiveSheet.PivotTables("PivotTable1").PivotFields("Dept Head") .Orientation = xlRowField .Position = 1 End With 

Answer 1

This worked for me (XL2007):

 Sub Tester() With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ActiveSheet.UsedRange) .CreatePivotTable TableDestination:="Sheet1!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 End With With Sheet1.PivotTables("PivotTable1") .PivotFields("Dept Head").Orientation = xlColumnField .PivotFields("Program Name").Orientation = xlRowField .AddDataField .PivotFields("Cost"), "Sum of cost", xlSum End With End Sub 

Make sure you do not already have an existing conflicting pivot cache / table.