Type mismatch error when creating a pivot table in Excel with VBA

Question

I'm trying to put a macro together that will make a simple pivot table using the data from an active worksheet. When I try to run it, I receive a type mismatch error. When I start the debugger, the first section is highlighted: ActiveWorkbook.PivotCaches through xlPivotTableVersion10. Initially, the TableDestination was blank and I thought that might be the problem, but after adding a 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

Solution

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 don't already have an existing conflicting pivot cache/table.

Comments

[JP D]

Thanks for the help. It still gives me Run-time error '13': Type mismatch on this piece: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ActiveSheet.UsedRange).CreatePivotTable _ TableDestination:="Sheet1!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

[Rachel Hettinger]

1) What version of Excel are you running? 2) Have you verified that ActiveSheet.UsedRange is valid? 3) Do you have a Sheet1?

[Tim Williams]

@user: you have a lot going on in that first line. Try splitting it up (see my edit). Where does the error occur if you do that?

[JP D]

@RachelHettinger 1) I've tried in both 2007 and 2010. 2) I have verified that ActiveSheet.UsedRange is valid. 3) I tried it both with a Sheet1 already created and without, both with the same result

[JP D]

@TimWilliams I split the first line up and the error occurs at With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ActiveSheet.UsedRange)

[Tim Williams]

Seems like a problem with the source data? Have you tried substituting a hard-coded range in place of UsedRange ?

[JP D]

Ah! That did it. I'll need to do some research about UsedRange, but I was able to get it to work first by hardcoding a range, and then by adding Dim Rng As Range Set Rng = ActiveSheet.UsedRange ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=Rng and then just making the source data "Range1"